Using single-precision points in PostGIS

single-precision-points.sql

-- 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))