-- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- Enable PostGIS Advanced 3D -- and other geoprocessing algorithms -- sfcgal not available with all distributions CREATE EXTENSION postgis_sfcgal; -- fuzzy matching needed for Tiger CREATE EXTENSION fuzzystrmatch; -- rule based standardizer CREATE EXTENSION address_standardizer; -- example rule data set CREATE EXTENSION address_standardizer_data_us; -- Enable US Tiger Geocoder CREATE EXTENSION postgis_tiger_geocoder; -- Create table with spatial column CREATE TABLE geotable ( id SERIAL PRIMARY KEY, geom GEOMETRY(Point, 26910), name VARCHAR(128) ); -- Add a spatial index CREATE INDEX geotable_gix ON geotable USING GIST (geom); -- Add a point INSERT INTO geotable (geom) VALUES ( ST_GeomFromText('POINT(-126.4 45.32)', 26910) ); -- Query for nearby points SELECT id, name FROM geotable WHERE ST_DWithin( geom, ST_GeomFromText('POINT(0 0)', 26910), 1000 ); INSERT INTO geotable ( geom, name ) VALUES ( ST_GeomFromEWKT('SRID=26910;POINT(-126.4 45.32)'), 'A Place' ); CREATE TABLE global_points ( id SERIAL PRIMARY KEY, name VARCHAR(64), location GEOGRAPHY(POINT,4326) ); INSERT INTO global_points (name, location) VALUES ('London', ST_GeographyFromText('SRID=4326;POINT(0 49)') );