[postgis-users] Fastest way to load an area of ways

Hens,Markus M. m.hens at student.fontys.nl
Thu May 12 01:47:39 PDT 2011


Hi all,

My name is Markus and I am doing my bachelor thesis on an OpenStreetMap Routing topic.
Therefore I use a postGIS db to store the osm data. Actually I have a dump of the data of Germany.

For my routing I cache areas. These areas I access like this:

SELECT id, version, tags, nodes FROM ways WHERE linestring && ST_SetSRID('BOX3D(9.00000000 50.0000000, 9.005000 50.0050000)'::box3d,4326)  AND(  
						'highway=>motorway'::hstore <@ tags
						OR 'highway=>motorway_link'::hstore <@ tags
						OR 'highway=>trunk'::hstore <@ tags
						OR 'highway=>trunk_link'::hstore <@ tags
						OR 'highway=>primary'::hstore <@ tags
						OR 'highway=>primary_link'::hstore <@ tags
						OR 'highway=>secondary'::hstore <@ tags
						OR 'highway=>secondary_link'::hstore <@ tags
						OR 'highway=>tertiary'::hstore <@ tags
					        OR 'highway=>unclassified'::hstore <@ tags
						OR 'highway=>residential'::hstore <@ tags
						OR 'highway=>living_street'::hstore <@ tags
					);

The x and y of the box3d variate but every time have the same range of 0.005. Linestring is  the gist index. I did a Vacuum. 
Table (ways) has 9040062 rows. 

In my oppinion, the query is still slow. So my question to you is: Is this the best way to cache such an area of ways or is there an faster alternative?


Best regards, 

Markus Hens


More information about the postgis-users mailing list