Simple test for PostGIS extension for PostgreSQL

test_postgis.sql

-- Show PostGIS version
SELECT PostGIS_version();

-- Create table
CREATE TABLE global_points (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location geography(POINT,4326)
);

-- Create index
CREATE INDEX global_points_gix ON global_points USING GIST ( location );

-- Add some data into the test table
INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');
INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;POINT(-109 29)');
INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;POINT(0 49)');

-- select all data
SELECT * FROM global_points;
-- NAD 83 lon/lat
SELECT 'SRID=4269;POINT(-123 34)'::geography;
-- NAD27 lon/lat
SELECT 'SRID=4267;POINT(-123 34)'::geography;
-- A distance query using a 1000km tolerance
SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::geography, 1000000);
-- Distance calculation using GEOGRAPHY
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)'::geography);
-- Distance calculation using GEOMETRY
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)'::geometry);

-- Drop table
DROP TABLE global_points;