[GIS] PostGIS – geocode and address standardization

geocodingpostgispostgresqlreverse-geocoding

Can you please help me ( I am not professional, If it is possible some easy explanation) how to proceed with address standardization and geocoding with Open Street Maps data (outside the US) in PostGIS?

I've add osm data into PostGIS over osm2pgsql, but I edit style file for columns like street, country code, city … So I think I have all important informations about address. What should I do next? I need some steps how to proceed, and I next study required technologies.

I am working on Windows.

Best Answer

PostGIS TIGER Geocoder was built for the US but the functions could be altered by you to process other country data sets. The TIGER data set contain roads (lines) with address information along with tables for city, county and state data which the functions of the Geocoder utilize.

Pleases see this link: http://postgis.net/docs/manual-dev/Extras.html

If your road network already contains address information then you should be able to implement a geocoder.

Related Solutions

PostGIS – Creating Columns Using osm2pgsql Hstore Tags

You can transfer the desired hstore key/value data to new columns:
1-Create the desired columns (ex. addres, city, key1, key2, keyn)
2-Run:

UPDATE table 
SET 
address=hstorecolumn->'address',
city=hstorecolumn->'city',
key1=hstorecolumn->'key1',
key2=hstorecolumn->'key2',
keyn=hstorecolumn->'keyn';

Or you can simply get used to hstore, it works pretty well and I have so many good results with it that I can say IMO "hstore changed my life". lol

EDIT:
An example on how to use hstore:
SELECT streetdatatable.hstoredatacolumn -> 'address', streetdatatable.hstoredatacolumn -> 'city' FROM someschema. streetdatatable

It will show you all the address and cities values.

[GIS] Reverse geocoding using OSM data imported into PostGIS

It's easy to get unexpected effects running an UPDATE that involves a join. Deep in the PostgreSQL docs for UPDATE, you can find the following warning:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Since your original query is potentially generating many street matches for each point, you're updating the point with a random one of those matches. The fact that you're ordering the subquery doesn't help, unfortunately.

You can fix the problem by putting a DISTINCT in your FROM clause to make sure that you only get one match or, perhaps more directly, putting a LIMIT 1 after your ORDER BY.

A nice way to avoid the problem (that isn't much help here) is to pull your join out of the FROM and turn it into an expression after SET, ie:

UPDATE tower
   SET nearest_id = (SELECT id FROM planet_osm_point.way
                     WHERE ST_DWithin(tower.geom, way.geom, 0.01)
                     ORDER BY ST_Distance(tower.geom, way.geom) ASC
                     LIMIT 1);

This method has the advantage that your query will fail if your subquery returns more than one row. But if you need to fetch more than one column from your subquery, I don't think there's a way to take advantage of this.

Best Answer

Related Solutions

PostGIS – Creating Columns Using osm2pgsql Hstore Tags

[GIS] Reverse geocoding using OSM data imported into PostGIS

Related Question