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)