Can i do the following, using MsSqlSpatial?

Oct 20, 2007 at 5:53 AM
Edited Oct 20, 2007 at 5:55 AM
Hello folks,
We have an Sql2005 database x64bit. We have a table called tblLatLong which is a simple table of IdLatLong, Latitude (varchar) and Longitude (varchar). We have some other tables that have a foreign key to the tblLatLong table. For example, we might have a table of users, and they can provide the lat/long of where they live, for example.

Now, could I use this sql extension to:
  • add it to an existing database
  • query my data. Say for example, all the users in a 10KM radius of a centre point? All the users in a polygon (defined as a list of latlongs ... i'm assuming the the list of lat longs form a polygon of some weird shape .. definately not necessarily a square or rectangle).

Currently we have some poor performing custom SQL queries that do some of the calcs above, but not all.

The magic this project might have could make me cry with joy if it can do the above examples.

cheers, looking forward to some feedback.

edit: cleaned up text.
Oct 20, 2007 at 6:58 AM
Since it's only point data, you don't need MsSqlSpatial for that (that would just add overhead to this). Instead all you need is to apply some proper practices with SQL.
  1. First of all, you don't want to use varchar for long/lat. Use a real value. That will save you from converting the values to numbers all the time. There's absolutely no reason to store a number as a character.
  2. Secondly create indexes on both columns.
  3. Third, first use a between query to select the points +/- 10km north/south and east/west to quickly limit the initial number of results, and then on the second part of the query do the distance calculations (note that because you are in long lat, you need to do conversions, but you can easily accomplish that with a stored procedure).
Oct 20, 2007 at 7:56 AM
Hi SharpGIS, thanks for the prompt reply.

It's not probs for me to convert the varchar's over to the sql datatype 'REAL.', then i can add an index on each field. Kewl.
For the third part, i thought maybe MsSqlSpatial might have some helper methods or stuff which can make my stored procs a lot easier and prolly more efficient to run. I'm not what you mean (in sql query examples) by using a BETWEEN query for the first radius example i gave, and i definately don't understand what you ment (sorry blush) about the second example i suggested. Would this be a good place to ask questions about sql spatial sql .. or just the sql extensions if they can do this for us?
Oct 21, 2007 at 9:29 PM
Edited Oct 21, 2007 at 9:30 PM
Between queries are explained here.

Regarding the 3rd part, MsSqlSpatial does have some code for reprojecting into a different coordinate system where you can to some extent do a query in for instance meters with a reasonable accuracy, but I think this will just add a lot of overhead to an already fairly simple use-case (and it does sound like you want performance). Also applying a projection can actually make the problem a lot more complex if you deal with large areas, where the distortions in the projections get really large (see fx. this).