3

i'm building function for reverse geocoding in Postgres using PostGIS function's. For now i was able to clean up all the data that was useless to me(rivers, buildings,etc.). I got 3 tables(world_osm_roads - in witch are all the roads, world_osm_places - in witch are all the cities and world_osm_countrys - in witch are all the countrys). Now i can get the record with country, city and road, in under 10ms, but it's still to slow, for the web service, to serve multiple request per second. Here is the function

    CREATE OR REPLACE FUNCTION public.nearest_neighbor_way_v1(lat float8, lon float8)
  RETURNS SETOF nn_road
AS
$BODY$
  DECLARE 
        gps_point geometry;

      BEGIN
      IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
        gps_point := st_geomfromtext('POINT(' || $2 ||' '|| $1 || ')', 4326);

        RETURN QUERY 
          SELECT 
            iso2                    ::character varying,
            place_name              ::character varying,
            road_id,
            road_name               ::character varying,
            road_type               ::character varying,
            max_speed_kmh,
            distance,
            ST_x(closest_point)     ::numeric(10,7),
            ST_Y(closest_point)     ::numeric(10,7)
          FROM (
                SELECT 
                  wop.name place_name,
                  road_id,
                  road_name,
                  max_speed_kmh,
                  closest_point,
                  distance,
                  road_type
                FROM (
                       SELECT 
                         road_id,
                         road_name,
                         road_type,
                         max_speed_kmh,
                         st_closestpoint(way,gps_point) closest_point,
                         st_distance(gps_point, way) distance,
                         way
                       FROM world_osm_roads
                       WHERE st_expand(gps_point, 0.0003) && way AND st_dwithin(gps_point, way, 0.0003)
                       ORDER BY distance
                       LIMIT 1
                     ) a,
                     (SELECT name, way FROM world_osm_places WHERE st_expand(gps_point, 0.05) && way) wop
                  WHERE st_contains(wop.way,closest_point) 
                  ORDER BY ST_Perimeter(wop.way)
                  LIMIT 1
               ) c,
               (SELECT iso2, geom FROM world_osm_countrys WHERE st_expand(gps_point, 0.05) && geom) wocy
          WHERE st_contains(wocy.geom,closest_point)
          LIMIT 1;
          END IF;
        RETURN;
    END
$BODY$
LANGUAGE plpgsql VOLATILE;

Are there any suggestion how to make my function faster?

EDIT:

As Jakub Kania suggested i call the EXPLAIN ANALYZE and got this results:

"Limit  (cost=17.82..26.13 rows=1 width=94) (actual time=0.141..0.141 rows=0 loops=1)"
"  ->  Nested Loop  (cost=17.82..26.13 rows=1 width=94) (actual time=0.140..0.140 rows=0 loops=1)"
"        Join Filter: ((world_osm_countrys.geom && (st_closestpoint(world_osm_roads.way, '0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry))) AND _st_contains(world_osm_countrys.geom, (st_closestpoint(world_osm_roads.way, '0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry))))"
"        ->  Limit  (cost=17.68..17.68 rows=1 width=5664) (actual time=0.139..0.139 rows=0 loops=1)"
"              ->  Sort  (cost=17.68..17.68 rows=1 width=5664) (actual time=0.139..0.139 rows=0 loops=1)"
"                    Sort Key: (st_perimeter(world_osm_places.way))"
"                    Sort Method: quicksort  Memory: 25kB"
"                    ->  Nested Loop  (cost=9.37..17.67 rows=1 width=5664) (actual time=0.130..0.130 rows=0 loops=1)"
"                          Join Filter: ((world_osm_places.way && (st_closestpoint(world_osm_roads.way, '0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry))) AND _st_contains(world_osm_places.way, (st_closestpoint(world_osm_roads.way, '0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry))))"
"                          ->  Limit  (cost=9.08..9.09 rows=1 width=245) (actual time=0.129..0.129 rows=0 loops=1)"
"                                ->  Sort  (cost=9.08..9.09 rows=1 width=245) (actual time=0.129..0.129 rows=0 loops=1)"
"                                      Sort Key: (st_distance('0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry, world_osm_roads.way))"
"                                      Sort Method: quicksort  Memory: 25kB"
"                                      ->  Index Scan using roads_index on world_osm_roads  (cost=0.55..9.07 rows=1 width=245) (actual time=0.122..0.122 rows=0 loops=1)"
"                                            Index Cond: (('0103000020E610000001000000050000008CB96B09F95F53C07EFE7BF0DA2D45408CB96B09F95F53C0D42EA699EE2D454062A1D634EF5F53C0D42EA699EE2D454062A1D634EF5F53C07EFE7BF0DA2D45408CB96B09F95F53C07EFE7BF0DA2D4540'::geometry && way) AND (way && '0103000020E610000001000000050000008CB96B09F95F53C07EFE7BF0DA2D45408CB96B09F95F53C0D42EA699EE2D454062A1D634EF5F53C0D42EA699EE2D454062A1D634EF5F53C07EFE7BF0DA2D45408CB96B09F95F53C07EFE7BF0DA2D4540'::geometry))"
"                                            Filter: (('0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry && st_expand(way, 0.000299999999999999974::double precision)) AND _st_dwithin('0101000020E6100000772D211FF45F53C0A91611C5E42D4540'::geometry, way, 0.000299999999999999974::double precision))"
"                                            Rows Removed by Filter: 2"
"                          ->  Index Scan using places_index on world_osm_places  (cost=0.28..8.30 rows=1 width=5587) (never executed)"
"                                Index Cond: ('0103000020E61000000100000005000000AA605452276353C043B0AA5E7E274540AA605452276353C00F7D772B4B34454044FAEDEBC05C53C00F7D772B4B34454044FAEDEBC05C53C043B0AA5E7E274540AA605452276353C043B0AA5E7E274540'::geometry && way)"
"        ->  Index Scan using countrys_index on world_osm_countrys  (cost=0.14..8.16 rows=1 width=119944) (never executed)"
"              Index Cond: ('0103000020E61000000100000005000000AA605452276353C043B0AA5E7E274540AA605452276353C00F7D772B4B34454044FAEDEBC05C53C00F7D772B4B34454044FAEDEBC05C53C043B0AA5E7E274540AA605452276353C043B0AA5E7E274540'::geometry && geom)"
"Total runtime: 0.268 ms"
  • EXPLAIN ANALYZE would be helpful when asking for performance help. – Jakub Kania Feb 25 '15 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.