locked
logic for geocoding RRS feed

  • Question

  • I need to provide geocoding abilities to customers in the Cayman Islands. I find that Bing and Google do not provide accurate geocoding in that area (at best it will get me to the centroid of the street, but not necessarily even on the block of the address). I have been given data in shapefile format that lists the lat/long of all buildings and all streets in the islands. I was planning on importing that data to SQL Server 2008 using the Shape2SQL tool. Does anyone have any reference to how geocoders perform their lookups? I can't find any algorithms online.
    Wednesday, September 16, 2009 10:00 PM

Answers

  • If you are using Geometry, you can call the STCentroid() method:

    DECLARE

     

    @g geometry;

    SET

     

    @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))', 0);

    SELECT

     

    @g.STCentroid()



    If you are using Geography, there is no centoid, but you can get the center of the envelope:

    DECLARE

     

    @g geography;

    SET

     

    @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);

    SELECT

     

    @g.EnvelopeCenter()

    Thursday, September 17, 2009 9:28 PM
  • That expansion from "Rd" to "Road" is exactly the sort of thing that can be handled by Full-Text Search (although it's not the only way) - try here for a good introduction: http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features,-part-2/

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, September 18, 2009 4:31 PM
    Answerer

All replies

  • Do you mean geocoding, or reverse-geocoding?

    For geocoding, it's simply a case of providing separate entry fields for e.g. address line 1, address line2 , town, and country, and then supplying these to a stored procedure that searches the table for these values and returns the appropriate point - it's not really a spatial query, but just a normal query of text data. If you want to do "fuzzy" matching of addresses, you might find full-text search helpful.

    For reverse-geocoding, you need a query that accepts as input a point location, and returns the closest feature to that point - i.e. a "nearest neighbour" query. These have been discussed a couple of times on this forum and there are various approaches available that mostly use STDistance() in conjunction with a filter search to narrow down a list of candidates. A search of the forum should bring up the discussion.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, September 17, 2009 8:38 AM
    Answerer
  • Thanks. I am only interested in geocoding and not reverse geocoding. Not really being a database person, I am not aware of full-text search so I am going to look up some information on that.

    I have building polygons which I have successfully placed in the database. For geodoing purposes, though, I only want to return the centroid. Any ideas how to do this. Actually, I really only need to store the centroid in the database.

    Thursday, September 17, 2009 1:59 PM
  • If you are using Geometry, you can call the STCentroid() method:

    DECLARE

     

    @g geometry;

    SET

     

    @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))', 0);

    SELECT

     

    @g.STCentroid()



    If you are using Geography, there is no centoid, but you can get the center of the envelope:

    DECLARE

     

    @g geography;

    SET

     

    @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);

    SELECT

     

    @g.EnvelopeCenter()

    Thursday, September 17, 2009 9:28 PM
  • Thanks. I ended up using EnvelopeCenter. Now I'm looking into how to parse the address the user enters into something I can use to search. For example, the user might type in Road, but Rd is in the database, Beach, but Bch is in the database, etc.
    Friday, September 18, 2009 3:32 PM
  • That expansion from "Rd" to "Road" is exactly the sort of thing that can be handled by Full-Text Search (although it's not the only way) - try here for a good introduction: http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features,-part-2/

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, September 18, 2009 4:31 PM
    Answerer