[OSM-dev] API for local Postgis installs?

[Jon Burgess]

On Mon, 2007-10-22 at 11:26 +0100, Nick Whitelegg wrote:
> >I'm not sure this is possible - osm2pgsql is a lossy conversion, so
> >it's unlikely you can backtrack. It might work for simple features but
> >others e.g polygons, relations it might not be possible.
> 
> Presumably for nodes and ways it would be OK though (and most requirements 
> would just be for nodes and ways) as long as the OSM ID is in the 
> database.

There is an osm_id column in all the tables. For 99.9+% of cases the ID
corresponds to the OSM node or way. The IDs of nodes which make up a way
are not stored.

The only objects in the DB which violate this are:
- Points with amenity=parking are created using the ID of a parking area
way
- ways which form part of a multipolygon get added to the polygon table
as a single entry using the ID of the relation instead of the individual
ways.


PostGIS allows lots of geometric queries. Here is a worked example using
the Postgres command line. It should be trivial to run equivalent
commands in Perl,PHP,Ruby or whatever.


Q. What are the nearest roads or other linear features within 250m of
Buckingham Palace?

First locate the co-ordinates of Buckingham Palace:

gis=> select osm_id,astext(way) from planet_osm_point where name='Buckingham Palace';
  osm_id  |                  astext
----------+-------------------------------------------
 25524277 | POINT(-15863.4059243102 6676913.74599225)


Next perform geometry query to locate the nearest linear features:

gis=> SELECT osm_id,highway,name,amenity, Distance(GeomFromText('POINT(-15863.4059243102 6676913.74599225)',4326),way) as dist FROM planet_osm_line WHERE way &&
          Expand(GeomFromText('POINT(-15863.4059243102 6676913.74599225)',4326),250) AND
          Distance(GeomFromText('POINT(-15863.4059243102 6676913.74599225)',4326),way) < 250 ORDER by dist;
 osm_id  |   highway    |          name          | amenity |       dist
---------+--------------+------------------------+---------+------------------
 5022021 | footway      |                        |         | 146.111061480341
 4494887 |              | Victoria Line          |         | 171.822115771729
 4237856 | primary      | Buckingham Palace Road |         |  186.49016863659
 4255599 | primary      | Birdcage Walk          |         | 187.139780399247
 4258071 | unclassified |                        |         | 193.896976254522
 7848383 | footway      |                        |         |  194.35550493035
 5022026 | footway      |                        |         | 195.174159600266
 3996623 | unclassified | Constitution Hill      |         | 226.513408702349
 4258073 | unclassified | Buckingham Gate        |         | 229.889311788846
 4258068 | unclassified | Stafford Place         |         | 234.704855030173
 4258070 | footway      |                        |         | 241.149201194979
(11 rows)

The example above could be changed to give the nearest POI's, roads etc
from a given point in the map providing you can obtain the equivalent
mercator co-ordinates. It should be relatively straightforward to
calculate the mercator co-ordinates if you know the position of the
mouse click within a specified x/y/z tile.

Understanding the geometric queries is not easy (and I'm certainly no
expert) but Postgis will generally do a lot of the hard work for you.
For more details on Postgis geometries queries you should probably first
read http://postgis.refractions.net/docs/ 

	Jon