This project is read-only.

newbie: lat/lng checking SQL syntax

Topics: User Forum
Dec 17, 2006 at 9:47 PM
Folks,

I am very green when it comes to db spatial extensions so bear with me.

Here's what I want to do:

I have successfully installed MsSqlSpatial and imported a shapefile for North America. What I am attempting to do is produce a recordset from table A that has latitude, longitude, and State/Province columns (plus many other columns) where the indicated latitude and longitude do not fall within the bounds of individual States/Provinces as determined from the imported shapefile (table B), thus building a simple geocoding check of sorts. The imported shapefile (table B) also has this State/Province column. So, I am looking to join the State/Province column in A to that in B and produce a recordset of the 'problem' records in table A. Is such a thing possible? If so, can someone provide an example SQL query to accomplish this?

Thanks,

Dave
Dec 18, 2006 at 5:06 AM
In case that last post made absolutely no sense, let me clarify a bit (been doing more reading)...

I'm looking to reverse geocode (latitude & longitude columns) a recordset in a non-spatial table whose State or Province designation (State/Province column) does not equal that in the spatial table.

i.e. find all records whose latitude and longitude fall outside e.g. Colorado yet whose State/Province cell = Colorado as entered by the client.

I suspect this will require some sort of stored procedure loop, but the eventual product is an AJAX app, so the SQL script must be in vbscript as raw SQL in the form "SELECT * FROM..."
Dec 19, 2006 at 1:05 AM
Well, I have something that now works, but it is hellishly slow so am now seeking some advice on how to best optimize it. It currently takes about 1 sec to rip through ~60 records...not good.

Here's a scalar function I created to spit out the State from inputted latitude and longitude parameters:

FUNCTION ST.GetSTATEABB
(@long float, @lat float)
RETURNS varchar(2)
AS
BEGIN
DECLARE @State varchar(2)
SELECT @State = (
SELECT STATEABB
FROM ST.RelateQuery('boundaries', 'thegeom', ST.GeomFromText('POINT(' + CAST(ISNULL(@long,0) AS nvarchar(15)) + ' ' + CAST(ISNULL(@lat,0) AS nvarchar(15)) + ' )', - 1), 'Within') AS STq
INNER JOIN dbo.boundaries AS STt ON STq.oid = ST_t.oid)
RETURN @State
END

...and the query that uses this function...

SELECT dbo.CollectionData_Shorthouse.*
FROM dbo.StateProvinces
INNER JOIN
dbo.CollectionData_Shorthouse
ON dbo.StateProvinces.StateProvince = dbo.CollectionData_Shorthouse.StateProvince
AND dbo.StateProvinces.Code <> ST.GetSTATEABB(dbo.CollectionDataShorthouse.Longitude, dbo.CollectionDataShorthouse.Latitude)
Dec 19, 2006 at 8:30 PM
I would like to give you a more detailed answer, but in this moment I just can tell you check out the use of APPLY keyword. See:
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
I hope this helps.

Best regards,
Ricardo Stuven.
Apr 13, 2007 at 1:49 AM
Did you work out how to use the APPLY keyword?

I'm a GIS guy - rather than a database specialist (all my tsql is self taught)
The sql GetSTATEABB function makes sense to me...how would the query be rewritten to use APPLY

Thanks
Alex
Apr 13, 2007 at 2:56 PM
Dave and Alex:

For this simple case you don't need to create a function getSTATEABB to do row by row comparison. All you need is to create a temp table to join Table A and B by StateProvince column. You need to select all columns you need and most importantly include the lat/long in Table A and the thegeom in Table B. Then you can use “not exist” in SQL to exclude those rows falling within StateProvince boundary polygon. If you still have problem, please let me know. I send you the query. I am kind of busy now and I will have more time on the weekend.

APPLY will work but it is not very efficient way to do it.

Cheers,
Adrian