Hi Gregory,
Thanks for the tips. I re-imported as you suggest, with just the highway and
maxspeed tags. I'm getting the expected results when I use your query, e.g.:
SELECT highway, maxspeed , ST_AsText(way) as polyline
FROM planet_osm_line
WHERE ST_DWithin(ST_SetSRID(ST_Point(-79.587976, 43.579156),4326),
ST_Transform(way, 4326), 0.00013)
AND highway is not null
ORDER BY ST_Distance(ST_SetSRID(ST_Point(-79.587976, 43.579156),4326),
ST_Transform(way, 4326)) LIMIT 5
However, performance is quite bad. About 500ms per query. There's a GiST index
on way:
sl_can=# \d+ planet_osm_line;
Table "public.planet_osm_line"
Column | Type | Modifiers | Storage | Description
----------+----------+-----------+----------+-------------
osm_id | integer | | plain |
highway | text | | extended |
maxspeed | text | | extended |
way | geometry | | main |
Indexes:
"planet_osm_line_index" gist (way)
Has OIDs: no
...But it seems not to be used. Any idea why? Here's EXPLAIN ANALYZE:
Limit (cost=485325.17..485325.17 rows=1 width=706) (actual
time=616.312..616.313 rows=2 loops=1)
-> Sort (cost=485325.17..485325.17 rows=1 width=706) (actual
time=616.311..616.312 rows=2 loops=1)
Sort Key:
(st_distance('0101000020E61000004EB51666A1E553C01EA4A7C821CA4540'::geometry,
st_transform(way, 4326)))
Sort Method: quicksort Memory: 25kB
-> Seq Scan on planet_osm_line (cost=0.00..485325.16 rows=1
width=706) (actual time=311.978..616.298 rows=2 loops=1)
Filter: ((highway IS NOT NULL) AND (st_transform(way, 4326) &&
'0103000020E6100000010000000500000035255987A3E553C050C422861DCA454035255987A3E553C0EC832C0B26CA45406745D4449FE553C0EC832C0B26CA45406745D4449FE553C050C422861DCA454035255987A3E553C050C422861DCA4540'::geometry)
AND ('0101000020E61000004EB51666A1E553C01EA4A7C821CA4540'::geometry &&
st_expand(st_transform(way, 4326), 0.00013::double precision)) AND
_st_dwithin('0101000020E61000004EB51666A1E553C01EA4A7C821CA4540'::geometry,
st_transform(way, 4326), 0.00013::double precision))
Total runtime: 616.364 ms
(7 rows)
(And if I change the where clause to use a bounding box, the performance is
about the same)
sl_can=# EXPLAIN ANALYZE SELECT highway AS road, maxspeed , ST_AsText(way) as
polyline FROM planet_osm_line WHERE ST_Intersects(SetSRID('BOX3D(-79.58807769
43.57910442,-79.58788822 43.57921959)'::box3d,4326),ST_Transform(way, 4326))
ORDER BY ST_Distance(ST_SetSRID(ST_Point(-79.587976, 43.579156),4326),
ST_Transform(way, 4326)) LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=473305.32..473305.33 rows=3 width=706) (actual
time=550.622..550.623 rows=1 loops=1)
-> Sort (cost=473305.32..473305.33 rows=3 width=706) (actual
time=550.619..550.619 rows=1 loops=1)
Sort Key:
(st_distance('0101000020E61000004EB51666A1E553C01EA4A7C821CA4540'::geometry,
st_transform(way, 4326)))
Sort Method: quicksort Memory: 25kB
-> Seq Scan on planet_osm_line (cost=0.00..473305.30 rows=3
width=706) (actual time=276.875..550.608 rows=1 loops=1)
Filter:
(('0103000020E61000000100000005000000AA839B10A3E553C03D6FF81720CA4540AA839B10A3E553C0532016DE23CA4540A2A6E9F59FE553C0532016DE23CA4540A2A6E9F59FE553C03D6FF81720CA4540AA839B10A3E553C03D6FF81720CA4540'::geometry
&& st_transform(way, 4326)) AND
_st_intersects('0103000020E61000000100000005000000AA839B10A3E553C03D6FF81720CA4540AA839B10A3E553C0532016DE23CA4540A2A6E9F59FE553C0532016DE23CA4540A2A6E9F59FE553C03D6FF81720CA4540AA839B10A3E553C03D6FF81720CA4540'::geometry,
st_transform(way, 4326)))
Total runtime: 550.674 ms
(7 rows)
On 2012-08-24, at 4:17 AM, Gregory Williams wrote:
> This is probably a question better asked on the dev@ mailing list, but for
> now I’ll answer here.
>
> As David says you’ll need to ensure that you’ve actually imported the
> maxspeed tag. It isn’t imported by default. Edit the default.style file to
> include a line like this:
>
> way maxspeed text linear
>
> Ignoring the projection issues which David mentions below, this query would
> do the job:
>
> select
> maxspeed
> from planet_osm_line
> where highway is not null
> and ST_DWithin(ST_SetSRID(ST_Point(1.0753, 51.2817),4326),
> ST_Transform(way, 4326), 0.00013)
> order by
> ST_Distance(ST_SetSRID(ST_Point(1.0753,
> 51.2817),4326), ST_Transform(way, 4326))
> limit 1
>
> Replace the longitude and latitude values passed to the two ST_Point
> functions as appropriate.
>
> The ST_Transform functions with arguments of 4326 convert it to degrees. By
> default osm2pgsql stores data in the Google Spherical Mercator projection
> (900913).
>
> Gregory
>
> PS There are 1.609 km per mile, not the 1.62 that David mentions.
>
> From: David ``Smith'' [mailto:[email protected]]
> Sent: 23 August 2012 23:14
> To: [email protected]
> Subject: Re: [OSM-newbies] Howto: Query to get max speed limit of nearby
> road/highway
>
> The first question is, does your postGIS database contain the speed limit
> information? I'm assuming you used a utility called osm2pgsql to import the
> data. That program uses a plaintext file called something like style.txt
> which tells it which OSM tags are important for the database; you want to
> make sure "maxspeed" is in there, which it probably isn't by default; the
> only other tag you really need for this purpose is "highway", and the rest
> can probably go, to keep your database smaller. I hope you already figured
> this out.
>
> Now your database should have a table (among others) called something like
> lines, whose columns should include geometry, highway, and maxspeed. The
> values in the highway and maxspeed columns are strings, though the maxspeed
> values should "look like" numbers. It would be prudent to find maxspeed
> values of the form "## mph", extract the numerical part, and multiply by
> 1.62, then replace the original string value with that result. Probably not
> necessary, but possibly still prudent, discard any other maxspeed values that
> contain nonnumeric characters. Now all maxspeed values are strings that look
> like numbers, and are expressed in km/h.
>
> I imagine the postgresql functions include a way to query which linear
> features come within a certain distance of a given point, but I don't know
> the name or syntax or even that it exists with certainty. Unless you want to
> deal with reprojection at multiple places in the project, the distance will
> have to be specified in degrees (probably about 0.00013 degrees for your
> application), and your queries will likely be more sensitive to east-west
> distance than north-south distance (by a factor of about 1.4 for populated
> areas of Canada, tolerable in my opinion). You'll also want to filter out
> results whose maxspeed or highway columns are null, and then return the
> highest maxspeed value remaining in the results.
>
> On Aug 23, 2012 5:34 PM, "René Fournier" <[email protected]> wrote:
> So... I've imported 27 GB of canada.osm into PostGIS 1.5.4. Now I am trying
> to construct a query that, given a point (latitude, longitude), finds the max
> speed limit of the road it is on (or within 10 meters of — otherwise, return
> nothing). Can anyone help? I'm a total OSM noob, and have no idea how the
> data is structured or how best to extract this one piece of information.
> Thanks!
>
> ...Rene
> _______________________________________________
> newbies mailing list
> [email protected]
> http://lists.openstreetmap.org/listinfo/newbies
> _______________________________________________
> newbies mailing list
> [email protected]
> http://lists.openstreetmap.org/listinfo/newbies
_______________________________________________
newbies mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/newbies
