Sharpmap query issue?

Oct 25, 2014 at 10:51 AM
Edited Oct 25, 2014 at 10:56 AM
Hello everybody. I have postgis table.( image 1 ). And I want to display them on mapbox( sharpmap ).
I have a query like this:

image 1
SELECT * FROM roads WHERE country = 'TR' AND city= 'IST' AND roadtype='MOTORWAY' ;
So, I want to dipslay on my map ; counrty TR city ist and roadtype motorway.
But I coudnt do it.
I try to do this with PROVIDER. But there is no correct solution. because, postgis query only makes Where query.
SharpMap.Layers.VectorLayer VLyol = new SharpMap.Layers.VectorLayer("roads");
VLyol.DataSource = new SharpMap.Data.Providers.PostGIS(postgisconn, "roads", "geom", "id");
Vlyol.DefinitionQuery = "roadtype='motorway'";
This DefinitionQuery only runs with WHERE query.
But I need more query for my solution.

Also I try to do this with Npgsql
But it didnt work.
private DataTable dt = new DataTable();
private DataSet ds = new DataSet();
private NpgsqlConnection conn = new NpgsqlConnection("server=localhost;port=5432;user=postgres;pwd=hs427034;database=kgmdb");
 string sql = " SELECT * FROM roads WHERE country = 'TR' AND city= 'IST' AND roadtype='MOTORWAY' ";
 NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            dt = ds.Tables[0];
var fdt = new SharpMap.Data.FeatureDataTable(dt);
            VectorLayer vLayer = new VectorLayer("vLayer");
            vLayer.DataSource = new SharpMap.Data.Providers.GeometryFeatureProvider(fdt);
I can get dt = ds.Tables[0]; ( image 2 )
But i coundt convert it layer on mymap
How can I get ds.Tables[0] on my map

image 2
Oct 25, 2014 at 2:46 PM
as I may remember, a DefinitionQuery like "country = 'TR' AND city= 'IST' AND roadtype='MOTORWAY'" should work.
anyway, maybe a better solution is to create a postgis view that define your filter, and then use the view as data source
Marked as answer by huseyin8316 on 10/25/2014 at 12:41 PM
Oct 25, 2014 at 6:32 PM
While Diego is right, for the sake of performance you should consider extracting the data you need to a separate table.
Oct 25, 2014 at 7:43 PM
Thank you very much. It works.