Note: Coordinates are in (longitude latitude)
format
CREATE EXTENSION postgis;
ALTER EXTENSION postgis
UPDATE TO "VERSION_NUMBER"
SELECT PostGIS_Version();
CREATE TABLE cities
(
id serial PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
location geometry, //point
boundary geometry //polygon
);
INSERT INTO cities("name", "location", "boundary")
VALUES("Wa", ST_GeomFromEWKT('SRID=4326;POINT(-2.5441288 10.0611456)'), ST_GeomFromEWKT('SRID=4326;POLYGON((-2.523873 10.094610, -2.544815 10.051342, -2.489197 10.026663, -2.474091 10.055061, -2.484047 10.084470, -2.523873 10.094610))'));
SELECT * FROM cities WHERE ST_Contains("boundary", 'SRID=4326;POINT(lon lat)');
SELECT * FROM cities
WHERE ST_Within(boundary, ST_GeomFromText('POLYGON((-2.523873 10.094610, -2.544815 10.051342, -2.489197 10.026663, -2.474091 10.055061, -2.484047 10.084470, -2.523873 10.094610))', 4326));
SELECT ST_Buffer(ST_MakePoint(lon, lat)::geography, 4000);
SELECT id, name,latitude, longitude,
ST_SetSRID(ST_MakePoint(longitude::float, latitude::float), 4326) AS center
FROM places;
SELECT AddGeometryColumn('table', 'column', 4326, 'POINT', 2);
UPDATE <table> SET <geom column> = ST_SetSRID(ST_MakePoint(<lon column>, <lat column>), 4326);
SELECT ST_AsEWKT('0101000020E610000012D90759164CD0BF3F19E3C3EC351640');
SELECT ST_GeomFromText('POINT(lon lat)', 4326);
SELECT ST_GeomFromEWKT('SRID=4326;POINT(lon lat)');
SELECT ST_AsText( ST_MakeEnvelope(Xmin, Ymin, Xmax, Ymax, 4326) );
Xmin = South West Lat, Ymin = South West Lon, Xmax = North East Lat, YMax = North East Lon
SELECT ST_AsGeoJSON('0101000020E610000012D90759164CD0BF3F19E3C3EC351640');
SELECT * FROM towns
ORDER BY geom_column <-> (SETSRID(ST_MakePoint(lon, lat)), 4326) LIMIT 10