Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- create point table using single-precision coordinates
- CREATE TABLE pts (
- point_id serial,
- x real,
- y real
- );
- -- create a view that converts these into a PostGIS geometry
- CREATE VIEW pts_view AS
- SELECT point_id, ST_SetSRID(ST_MakePoint(x, y), 4326) AS geom FROM pts;
- -- create a functional index on the raw -> PostGIS conversion
- CREATE INDEX ON pts USING gist(ST_SetSRID(ST_MakePoint(x, y), 4326));
- -- index is activated in a regular PostGIS query
- EXPLAIN SELECT * FROM ne_10m_admin_0_countries a INNER JOIN pts_view b ON ST_Intersects(a.geom, b.geom);
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------
- -- Nested Loop (cost=0.14..229.57 rows=173 width=35400)
- -- -> Seq Scan on ne_10m_admin_0_countries a (cost=0.00..73.55 rows=255 width=35364)
- -- -> Index Scan using pts_st_setsrid_idx on pts (cost=0.14..0.60 rows=1 width=12)
- -- Index Cond: (a.geom && st_setsrid(st_makepoint((x)::double precision, (y)::double precision), 4326))
- -- Filter: _st_intersects(a.geom, st_setsrid(st_makepoint((x)::double precision, (y)::double precision), 4326))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement