Layer and SQL Queries

Topics: SharpMap v0.9 / v1.x, SharpMap v2.0, WinForms Controls
Sep 5, 2011 at 9:55 AM

Hello,

I'd like to run complexe SQL Queries (ie usins several tables, several operations on the geometries,...). I'm working here with SQL Server by it's the same problem with another database.

I am using things like that

 Dim prov As SharpMap.Data.Providers.SqlServer2008 = New SharpMap.Data.Providers.SqlServer2008(conn, nomTable, "geom", "ID" )
prov.DefinitionQuery = "pop>2000"

prov.Open()

 Dim layWorld As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer (AliasTable, prov)

and so on ....

That works well, but I can retreive only the geometry column (ie "geom"). If I want the get anything else (always geometry objects) like a buffer , that doesn't work.

The same thing if I try request like this : Select region.geom.STDifference((Select geom from...

In fact with DefinitionQuery, I can only define a simple WHERE clause and it is not possible to define the SELECT clause.

So is it possible to open table like this with a complex SQL query ?

Or I must open the table as datatable (With a SELECT ... FROM...WHERE...)  and get the geometry objects as feature by looping thru the datatable ?

Thanks in advance for your replies.

Eric

Eric

Coordinator
Sep 5, 2011 at 4:09 PM

Hello Eric, currently you can only limit your queries with the DefinitionQuery property.

If you define views on you database, you can use those for your layers as well. You may need to register them to the geometry_columns table (PostGis, SpatiaLite, ...) for SharpMap to work properly.

You can also try to derive the provider you need and add some constructor that takes the sql query as an argument. You must take care to return a unique id and a geometry column. Some providers also require setting the spatial reference id on construction. Please report your findings, we may add such functionality.

Hth FObermaier

Sep 6, 2011 at 9:50 AM

Thanks a lot

I will try to derive the provider, I will keep you inform

Eric

 

Coordinator
Sep 6, 2011 at 10:14 AM

Having thought about it a little longer:

There are several places in a providers code that build the sql query string sent to the database.
They are all more or less the same, so maybe you can refactor that to a function and if your constructor has the sql instead of a table name, you'll just use that....

Just an idea though...

Hth FObermaier