2

I'm entirely new to PostGIS and trying to store encoded_polylines to test some proximity searches with lat/long points. So, I created a simple table composed of a pkid, an encoded polyline, and a polyline containing the line strings of the encoded polyline.

So, the table is like below:

CREATE TABLE test(
    pkid bigint NOT NULL,
    encoded_polyline text COLLATE pg_catalog."default",
    polyline geometry,
    CONSTRAINT test_pkey PRIMARY KEY (pkid),
);

CREATE INDEX IF NOT EXISTS test_polyline_idx
ON test USING gist(polyline) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS test_polyline_geog_idx
ON test USING gist(geography(polyline)) TABLESPACE pg_default;

I've created 2 indexes on the polyline geom according this article

What is the best option if I want to test if a point is within a 10 km range from the polyline?

Method 1: only store the encoded polyline and use ST_LineFromEncodedPolyline

SELECT pkid FROM test
WHERE ST_DWithin(ST_SetSRID(ST_MakePoint(13.03,52.33), 4326)::geography, ST_LineFromEncodedPolyline(encoded_polyline)::geography, 10* 1609.34)

Method 2: Only store the unencoded polyline and query it directly

SELECT pkid FROM test
WHERE ST_DWithin(ST_SetSRID(ST_MakePoint(13.03,52.33), 4326)::geography, polyline::geography, 10* 1609.34)
1
  • 1
    Convert the encoded polyline to geography and store that in the table. Then you can use the index on that geography column. If you derive the geography from the encoded line, the index won't be available, and your queries will be slower.
    – jbalk
    Aug 4 at 16:40

0

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.