PostGIS notes

postgis_notes.sql

-- Convert a lat,long pair to a projected (British grid system) coordiantes
-- SRID 4326: WGS84 GPS coordiantes
-- SRID 27700: British National Grid
SELECT
   ST_X(ST_Transform(ST_SetSRID(ST_Point(long, lat),4326), 27700)) as x
  ,ST_Y(ST_Transform(ST_SetSRID(ST_Point(long, lat),4326), 27700)) as y
;                     
                     
-- Number of points from another within X meters (for BSG)
SELECT
	"id"
    ,COUNT(*)
FROM "data" data1
INNER JOIN "dataiku"."data_other" data2
	ON ST_DWithin(data1.geom, data2.geom, 1000) -- 1000 meters
GROUP BY "id"
;                     

                     
-- Find nearest point and distance http://tiny.cc/ng6syy
SELECT
  parcels.*,
  hydrants.cartodb_id as hydrant_cartodb_id,
  ST_Distance(geography(hydrants.the_geom), geography(parcels.the_geom)) as distance
FROM
  (SELECT DISTINCT ON (the_geom) *
   FROM parcelsshp
   WHERE pid IS NOT NULL) AS parcels
CROSS JOIN LATERAL
  (SELECT cartodb_id, the_geom
   FROM hydrantsshp
   ORDER BY parcels.the_geom_webmercator <-> the_geom_webmercator
   LIMIT 1) AS hydrants