Geometry vs Geography

-- Same lon/lat, different types
SELECT
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326) AS geom_4326,
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326)::extensions.geography AS geog;
 
-- Distance NYC to LA
SELECT extensions.ST_Distance(
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326)::extensions.geography,
  extensions.ST_SetSRID(extensions.ST_MakePoint(-118.2437, 34.0522), 4326)::extensions.geography
) AS meters_geog;
 
SELECT extensions.ST_Distance(
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326),
  extensions.ST_SetSRID(extensions.ST_MakePoint(-118.2437, 34.0522), 4326)
) AS degrees_geom_wrong_for_meters;

CREATE TABLE delivery_zones (
  id       serial PRIMARY KEY,
  name     text,
  -- City-scale polygon: local UTM for accurate geometry math
  zone_geom extensions.geometry(Polygon, 32618),  -- UTM 18N for NYC area
  -- Driver location queries: geography for meter radius from GPS
  service_geog extensions.geography(Polygon, 4326)
);
 
-- Store both when needed: geometry for editing in local projection,
-- geography for "within 2 km of driver" queries
CREATE INDEX delivery_zones_geog_gist
  ON delivery_zones USING gist (service_geog);
 
SELECT name
FROM delivery_zones
WHERE extensions.ST_DWithin(
  service_geog,
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.99, 40.75), 4326)::extensions.geography,
  2000
);

SELECT srid, auth_name, auth_srid, srtext
FROM spatial_ref_sys
WHERE srid IN (4326, 3857, 32618);

-- Reproject geometry between SRIDs
SELECT extensions.ST_Transform(
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326),
  32618
);
 
-- Geography from geometry (4326 only for geog cast)
SELECT extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326)::extensions.geography;