Sorry for posting too quick, seems I answered my own question.
Removing ST_Transform makes the query 100 times faster (about 5 ms). But...
should I be concerned about the projection issues at ~10 meters? What would you
suggest for making the query fast AND accurate?
On 2012-08-27, at 1:55 PM, René Fournier wrote:
> 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
_______________________________________________
newbies mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/newbies
