Created
June 5, 2024 14:26
-
-
Save CHERTS/f96ea0a83b26197fd7966ef9f3161e74 to your computer and use it in GitHub Desktop.
Simple test for PostGIS extension for PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment