init.sql (1207B) - Raw
1 -- schema 2 CREATE TABLE airports ( 3 gid SERIAL NOT NULL PRIMARY KEY, 4 name TEXT, 5 city TEXT, 6 country TEXT, 7 iata TEXT, 8 icao TEXT, 9 geom GEOMETRY, 10 latitude DOUBLE PRECISION, 11 longitude DOUBLE PRECISION, 12 altitude DOUBLE PRECISION, 13 timezone TEXT, 14 dst TEXT, 15 tz TEXT, 16 type TEXT, 17 source TEXT, 18 CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 3), 19 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL), 20 CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326) 21 ); 22 -- create index for faster spatial lookups 23 CREATE INDEX idx_geom ON airports USING GIN (geom); 24 25 -- import data from airports.dat 26 \copy airports(gid, name, city, country, iata, icao, latitude, longitude, altitude, timezone, dst, tz, type, source) FROM 'airports.dat' DELIMITERS ',' CSV; 27 28 -- put lat/lon/altitude to the "real" geom field 29 UPDATE airports 30 SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude, altitude * 0.3048),4326); 31 32 ALTER TABLE airports DROP COLUMN latitude; 33 ALTER TABLE airports DROP COLUMN longitude; 34 ALTER TABLE airports DROP COLUMN altitude; 35 ALTER TABLE airports DROP COLUMN type; 36 ALTER TABLE airports DROP COLUMN source;