This project is read-only.

Retreive table field based on latitude and longitude coordinates

Topics: Developer Forum, User Forum
Jul 7, 2008 at 6:03 PM

Hi – I have been developing an application which is based on GIS with ESRI maps. These maps have a table where resides all kind of information such as: address, city name, etc...

 

I have already installed the spatial functionality in SqlServer 2005 and imported the entire map inside the database. I need to get that info using some kind of spatial query and retrieve all the data that I have in the tables by passing LONGITUDE and LATITUDE. Please this is very urgent!! Is any complete manual of the MsSpatial project? Otherwise, can I use sharpmap to retrieve this data as kind of dataset??

Jul 8, 2008 at 1:09 PM
Edited Jul 8, 2008 at 1:10 PM
Hi Polaquito, MsSqlSpatial is an implementation of Simple Features For SQL from the Open Geospatial Consortium http://www.opengeospatial.org/standards/sfs.
Assuming that your shapefiles had the appropriate prj file when you imported them (and that you are using the trunk or a release not v2 dev which cannot do reprojection currently) the geometries should have been imported with the correct srid. If this is not the case you will have to update the geometries to the srid of the projection the data is actually in. e.g
UPDATE my_spatial_table SET my_geometry_column =  ST.SetSRID(my_geometry_column, real_srid)



Once your data is in the correct you can use
SELECT ST.AsBinary(ST.Transform(your_geometry_column, new_srid)) FROM your_geometry_table  

where new_srid is the number representing the projection you want to transform to (likely to be 4326 which is the WGS 1984 coordinate system used by most GPS devices)

Remember if you need to apply a where clause based on a topological relationship to your result set, you will either need to transform the input 'comparison' geometry or the stored geometry_column to the same projection before doing the operation.

It may make sense to update all your stored geometries to the projection you will be using them in to save repeated processing and improve performance.

hth jd


Jul 11, 2008 at 6:19 PM

Jhon,

Thank you for you response. Let me try to be more especific. I have a divice which send data through GPRS (cellular network). This device send the data as: LON -58.457195, LAT -34.613015. I use this coordinates to draw some info into a map wich resides on a SHAPE or MSSQL.

My georeference TABLES are like this (pretty similiar):

Id        StreetName            MaxY            MaxX            MinY            MinX            Geometry bla bla..
---------------------------------------------------------------------------------------------------------------------------
33    |    MyStreet 54  |     33.8888   |    54.8888       |   32.777    |     52.7777   |     bla bla...
34    |    Avenue4        |     33.4444   |    54.1111       |   32.777    |     52.7777   |     bla bla...

What I want to know is if there is some possible query/stored/function to execute and get this row as and show all the fields that the task is returning.

Example:

- SELECT StreetName  FROM myTable where FUNCTION...bla bla..
- EXEC STORED_COOR(LON, LAT)
- etc...

Is this is possible in some way??

 

Many thanks in advance!

Leandro.

Jul 12, 2008 at 12:40 PM
Hi Leandro, In Sql Management Studio/ Express expand the following:
[Databasename] >> Programmability>>Functions>>Table-valued Functions

and you will find a variety of functions which may be useful depending on what you are trying to do.

To turn your lon lat ordinates into a geometry use ST.Point(@lon, @lat) which you may need to buffer to get useful results. You would do this with ST.Buffer.

hth jd