User talk:JRA

From OpenStreetMap Wiki
Jump to: navigation, search

Hstore, Mapserver, WMS and GetFeatureInfo

Hstore.jpg

Quite a lot of nice things can be done with a new hstore option of osm2pgsql utility. This option imports all the tags from .osm format xml-file into PosgreSQL database. Therefore it is possible to render just everything that exists in OSM data from PostGIS.

In this example the rendering engine is Mapserver. The basemap layer comes from standard PostGIS tables which osm2pgsql tradionally is creating. On top of the basemap there is a point layer that is queried from the hstore column "keys". Hstore column is created by using the new "-k" switch of osm2pgsql.

The query that Mapserver is using for drawing the layer with red dots is

DATA "way from (select (way),osm_id,tags from osm_point where ((tags->'FIXME')is not null)) as foo using unique osm_id using srid=2393"

What is extremely nice is that the "tags" column can be used with the standard WMS GetFeatureInfo query. In this example the query is sent with QGis, and server sends back all the tags of the feature. This means that all the features on a map are clickable and server can send back all the tags attached to the feature by using the standard WMS protocol without any programming on client or server side.



OpenJUMP

I've noticed your nice OpenJUMP screenshot in the featured image proposal page (proposed by User:LH) and linked the currently non-existing OpenJUMP wiki page. Would be cool if you briefly explained the necessary steps there (importing osm into postgis + splitting into layers or is there an OSM plugin for OpenJUMP?). tnx --Stefanb 16:32, 14 January 2008 (UTC)

These steps are needed in order to get OSM data into OpenJUMP in Windows

1. Preparations:

- Install PostgreSQL and PostGIS and create a postgres user with the same name as you appear in Windows (the utility explained next connects always with the default user name).

- Acquire osm2pgsql.exe Windows executable made by Artem Pavlenko. It can be downloaded from http://artem.dev.openstreetmap.org/files/

- Download OSM dump; I use http://hypercube.telascience.org/planet/ for Scandinavian dumps, and http://download.geofabrik.de/osm/europe/ for a dump of Finland.


- Download OpenJUMP from openjump.org. I recommend the nightly build (but nightly build is broken at the moment).

2. Install the dump.

- run osm2pgsql like this: osm2pgsql -d osmdatabasename osmdumpname

Note! Osm2pgsql.exe cannot take dbhost, port, username or password. Therefore database must be found from localhost, port 5432, database must have a user with the same name than is the Windows account name, and database must be set so, that no password is required from this user. That is set in PosGIS pg_hba.conf file by setting "method" to "trust".

Everything will be automatic after that and the result is ready-to-use PostGIS tables '_point', '_line' and '_polygon' containing OSM data. With the Scandinavian dump this takes about 7 minutes with 3 GHz Pentium.

3. Read the data into OpenJUMP. OpenJUMP has native support for PostGIS. All you need is to define the datastore, that is to give server, post, dbname etc. so that OpenJUMP knows how to connect. After that all PostGIS layers in that database are selectable. And not only that, they are queryable too. So you can tell OpenJUMP to read in line data WHERE highway='secondary' and you know what you will get :) Or from the point data WHERE shop='supermarket', and you will get the supermarkets which are never drawn in either Mapnik or Osmarender maps. --JRA 13:32, 16 January 2008 (UTC)

PS.

Nowadays I am creating new tables from motorways, trunk ways etc. which are always needed. It makes the use of data in GIS software a bit easier. As an example, here is the script I am running after running osm2pgsql.exe.

update osm_point set way=transform(way,2393);

update osm_line set way=transform(way,2393);

update osm_polygon set way=transform(way,2393);

update geometry_columns set srid=2393 where f_table_name='osm_point';

update geometry_columns set srid=2393 where f_table_name='osm_line';

update geometry_columns set srid=2393 where f_table_name='osm_polygon';


DROP TABLE osm_motorway; CREATE TABLE osm_motorway WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='motorway' OR highway='motorway_link');

DROP TABLE osm_trunk; CREATE TABLE osm_trunk WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='trunk' OR highway='trunk_link');

DROP TABLE osm_primary; CREATE TABLE osm_primary WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='primary' OR highway='primary_link');

DROP TABLE osm_secondary; CREATE TABLE osm_secondary WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='secondary' OR highway='secondary_link');

DROP TABLE osm_tertiary; CREATE TABLE osm_tertiary WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='tertiary');

DROP TABLE osm_unclassified; CREATE TABLE osm_unclassified WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='unclassified');

DROP TABLE osm_residential; CREATE TABLE osm_residential WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='residential');

DROP TABLE osm_track; CREATE TABLE osm_track WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='track');

DROP TABLE osm_service; CREATE TABLE osm_service WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='service');

DROP TABLE osm_cycleway; CREATE TABLE osm_cycleway WITH OIDS TABLESPACE pg_default AS (SELECT osm_id, way, highway, ref, name FROM osm_line WHERE highway='cycleway');

=