equivalent code for SharpMap

Topics: User Forum
Mar 2, 2007 at 2:06 PM
Hi,

I have the following query, which works well if I type it into SQL Server Management Studio. It basically returns the rows in "meridianbuiltupareas" that are contained within a polygon retrieved from "meridiandistrictandcountyboundaries".

SELECT *
FROM ST.RelateQuery#dbo#meridian_builtup_areas#the_geom((SELECT the_geom FROM meridian_district_and_county_boundaries WHERE ons='00NE'), 'Contains')
WHERE importance=1

What I am now trying to do is use the above query in conjunction with SharpMapProvider to basically cut down on the amount of processing that I need to do on the data once it's left the database. So far, I have tried:

layer.DefinitionQuery = "ST.RelateQuery#dbo#meridian_builtup_areas#the_geom((SELECT the_geom FROM meridian_district_and_county_boundaries WHERE ons='00NE'), 'Contains')";

and many other combinations of code, but none of them have worked. I presume that DefinitionQuery is the correct place to put this, as it would appear to be refining the dataset in some way.

Can anyone tell me how I should be writing the above to make it work?

Thanks,

Dylan
Mar 2, 2007 at 6:43 PM
The definition query is applied to the where clause. You will have to set the tablename to:
"ST.RelateQuery#dbo#meridianbuiltupareas#thegeom((SELECT thegeom FROM meridiandistrictandcountyboundaries WHERE ons='00NE'), 'Contains')"
and the definition query to:
"importance=1"
Mar 2, 2007 at 8:35 PM
Ah, it's starting to make sense to me now. I didn't see the connection between the definition query and the where clause before. Cheers.
Mar 5, 2007 at 8:47 AM
Ok, to further complicate issues, when I set the table name to the above, I get an error message:

Incorrect syntax near '.'.
Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and where it originated
in the code.
 
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '.'.

I'm not sure what I should be doing here to solve this problem.

The code I am using looks like:

SharpMap.Data.Providers.MsSqlSpatial myData =  new SharpMap.Data.Providers.MsSqlSpatial(
    "Server=***;Database=***;Uid=***;Pwd=***",
    "ST.RelateQuery#dbo#meridian_builtup_areas#the_geom((SELECT the_geom FROM meridian_district_and_county_boundaries WHERE ons='00NE'), 'Contains')",
    "the_geom",
    "oid");
myData.DefinitionQuery = "importance=1";

Is there any way I can get SharpMap to return the actual query that it used as part of the error message? That may help me to figure out problems like this when they occur!
Mar 5, 2007 at 11:57 AM
The actual query being ran, after finding out I can use SQL Profiler to find this, is:

SELECT *, ST.AsBinary(ST.Centroid(the_geom))
AS sharpmap_tempgeometry
FROM ST.FilterQuery#ST#RelateQuery#dbo#meridian_builtup_areas#the_geom((SELECT the_geom FROM meridian_district_and_county_boundaries WHERE ons='00NE'), 'Contains')#the_geom(ST.MakeEnvelope(253379.500433333,340147.0002,313203.499766667,385014.9997,-1))
WHERE importance=1

This is a really odd looking query, imho, and I can't figure out what it's trying to do here!
Mar 5, 2007 at 4:31 PM
Hmm maybe you can't use it as a tablename then. You should create a view and query against that instead.
Mar 5, 2007 at 9:19 PM
Yes, that's the conclusion I came to. I tried using a view as one of my first solutions, but as solutions don't allow parameters to be passed, they weren't a suitable solution - the "ons" value is a variable set in the script. I actually realised, shortly before leaving work, that I could pre-process the data so that each point in the meridianbuiltupareas table is already joined with the corresponding polygon from meridiandistrictandcountyboundaries - ie. an extra column with the ons code of its parent polygon. That was all I'd have to do is add "importance=1 AND ons='00NE'" to the filter and it would do the job I was trying to do with MsSqlSpatial - it would probably be quicker too!