projects / busui.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
unified | plain
Refactor to use PostGIS database instead of gtfs tools
[busui.git] / lib / postgis.sql
blob:a/lib/postgis.sql -> blob:b/lib/postgis.sql
  -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  --
  -- $Id: postgis.sql.in.c 5385 2010-03-09 00:22:41Z pramsey $
  --
  -- PostGIS - Spatial Types for PostgreSQL
  -- http://postgis.refractions.net
  -- Copyright 2001-2003 Refractions Research Inc.
  --
  -- This is free software; you can redistribute and/or modify it under
  -- the terms of the GNU General Public Licence. See the COPYING file.
  --
  -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  --
  -- WARNING: Any change in this file must be evaluated for compatibility.
  -- Changes cleanly handled by postgis_upgrade.sql are fine,
  -- other changes will require a bump in Major version.
  -- Currently only function replaceble by CREATE OR REPLACE
  -- are cleanly handled.
  --
  -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
  -- INSTALL VERSION: 1.5.1
   
  SET client_min_messages TO warning;
   
  BEGIN;
   
  -------------------------------------------------------------------
  -- SPHEROID TYPE
  -------------------------------------------------------------------
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_spheroid_in(cstring)
  RETURNS spheroid
  AS '$libdir/postgis-1.5','ellipsoid_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_spheroid_out(spheroid)
  RETURNS cstring
  AS '$libdir/postgis-1.5','ellipsoid_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION spheroid_in(cstring)
  RETURNS spheroid
  AS '$libdir/postgis-1.5','ellipsoid_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION spheroid_out(spheroid)
  RETURNS cstring
  AS '$libdir/postgis-1.5','ellipsoid_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE TYPE spheroid (
  alignment = double,
  internallength = 65,
  input = spheroid_in,
  output = spheroid_out
  );
   
  -------------------------------------------------------------------
  -- GEOMETRY TYPE (lwgeom)
  -------------------------------------------------------------------
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_geometry_in(cstring)
  RETURNS geometry
  AS '$libdir/postgis-1.5','LWGEOM_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_geometry_out(geometry)
  RETURNS cstring
  AS '$libdir/postgis-1.5','LWGEOM_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_geometry_analyze(internal)
  RETURNS bool
  AS '$libdir/postgis-1.5', 'LWGEOM_analyze'
  LANGUAGE 'C' VOLATILE STRICT;
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_geometry_recv(internal)
  RETURNS geometry
  AS '$libdir/postgis-1.5','LWGEOM_recv'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_geometry_send(geometry)
  RETURNS bytea
  AS '$libdir/postgis-1.5','LWGEOM_send'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION geometry_in(cstring)
  RETURNS geometry
  AS '$libdir/postgis-1.5','LWGEOM_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION geometry_out(geometry)
  RETURNS cstring
  AS '$libdir/postgis-1.5','LWGEOM_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION geometry_analyze(internal)
  RETURNS bool
  AS '$libdir/postgis-1.5', 'LWGEOM_analyze'
  LANGUAGE 'C' VOLATILE STRICT;
   
  CREATE OR REPLACE FUNCTION geometry_recv(internal)
  RETURNS geometry
  AS '$libdir/postgis-1.5','LWGEOM_recv'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION geometry_send(geometry)
  RETURNS bytea
  AS '$libdir/postgis-1.5','LWGEOM_send'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE TYPE geometry (
  internallength = variable,
  input = geometry_in,
  output = geometry_out,
  send = geometry_send,
  receive = geometry_recv,
  delimiter = ':',
  analyze = geometry_analyze,
  storage = main
  );
   
  -------------------------------------------
  -- Affine transforms
  -------------------------------------------
   
  -- Availability: 1.1.2
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Affine(geometry,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8)
  RETURNS geometry
  AS '$libdir/postgis-1.5', 'LWGEOM_affine'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8,float8)
  RETURNS geometry
  AS '$libdir/postgis-1.5', 'LWGEOM_affine'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Availability: 1.1.2
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Affine(geometry,float8,float8,float8,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, $2, $3, 0, $4, $5, 0, 0, 0, 1, $6, $7, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, $2, $3, 0, $4, $5, 0, 0, 0, 1, $6, $7, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.2
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION RotateZ(geometry,float8)
  RETURNS geometry
  AS 'SELECT affine($1, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0, 1, 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_RotateZ(geometry,float8)
  RETURNS geometry
  AS 'SELECT affine($1, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0, 1, 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.2
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Rotate(geometry,float8)
  RETURNS geometry
  AS 'SELECT rotateZ($1, $2)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Rotate(geometry,float8)
  RETURNS geometry
  AS 'SELECT rotateZ($1, $2)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.2
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION RotateX(geometry,float8)
  RETURNS geometry
  AS 'SELECT affine($1, 1, 0, 0, 0, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_RotateX(geometry,float8)
  RETURNS geometry
  AS 'SELECT affine($1, 1, 0, 0, 0, cos($2), -sin($2), 0, sin($2), cos($2), 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.2
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION RotateY(geometry,float8)
  RETURNS geometry
  AS 'SELECT affine($1, cos($2), 0, sin($2), 0, 1, 0, -sin($2), 0, cos($2), 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_RotateY(geometry,float8)
  RETURNS geometry
  AS 'SELECT affine($1, cos($2), 0, sin($2), 0, 1, 0, -sin($2), 0, cos($2), 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Translate(geometry,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, 1, 0, 0, 0, 1, 0, 0, 0, 1, $2, $3, $4)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Translate(geometry,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, 1, 0, 0, 0, 1, 0, 0, 0, 1, $2, $3, $4)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Translate(geometry,float8,float8)
  RETURNS geometry
  AS 'SELECT translate($1, $2, $3, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Translate(geometry,float8,float8)
  RETURNS geometry
  AS 'SELECT translate($1, $2, $3, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.0
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Scale(geometry,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, $2, 0, 0, 0, $3, 0, 0, 0, $4, 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Scale(geometry,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, $2, 0, 0, 0, $3, 0, 0, 0, $4, 0, 0, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.0
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION Scale(geometry,float8,float8)
  RETURNS geometry
  AS 'SELECT scale($1, $2, $3, 1)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_Scale(geometry,float8,float8)
  RETURNS geometry
  AS 'SELECT scale($1, $2, $3, 1)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.0
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION transscale(geometry,float8,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, $4, 0, 0, 0, $5, 0,
  0, 0, 1, $2 * $4, $3 * $5, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_transscale(geometry,float8,float8,float8,float8)
  RETURNS geometry
  AS 'SELECT affine($1, $4, 0, 0, 0, $5, 0,
  0, 0, 1, $2 * $4, $3 * $5, 0)'
  LANGUAGE 'SQL' IMMUTABLE STRICT;
   
  -- Availability: 1.1.0
  -- Deprecation in 1.2.3
  CREATE OR REPLACE FUNCTION shift_longitude(geometry)
  RETURNS geometry
  AS '$libdir/postgis-1.5', 'LWGEOM_longitude_shift'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Availability: 1.2.2
  CREATE OR REPLACE FUNCTION ST_shift_longitude(geometry)
  RETURNS geometry
  AS '$libdir/postgis-1.5', 'LWGEOM_longitude_shift'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -------------------------------------------------------------------
  -- BOX3D TYPE
  -------------------------------------------------------------------
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_box3d_in(cstring)
  RETURNS box3d
  AS '$libdir/postgis-1.5', 'BOX3D_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- Deprecation in 1.5.0
  CREATE OR REPLACE FUNCTION st_box3d_out(box3d)
  RETURNS cstring
  AS '$libdir/postgis-1.5', 'BOX3D_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION box3d_in(cstring)
  RETURNS box3d
  AS '$libdir/postgis-1.5', 'BOX3D_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION box3d_out(box3d)
  RETURNS cstring
  AS '$libdir/postgis-1.5', 'BOX3D_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE TYPE box3d (
  alignment = double,
  internallength = 48,
  input = box3d_in,
  output = box3d_out
  );
   
  -- Temporary box3d aggregate type to retain full double precision
  -- for ST_Extent(). Should be removed when we change the output
  -- type of ST_Extent() to return something other than BOX2DFLOAT4.
  CREATE OR REPLACE FUNCTION box3d_extent_in(cstring)
  RETURNS box3d_extent
  AS '$libdir/postgis-1.5', 'BOX3D_in'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION box3d_extent_out(box3d_extent)
  RETURNS cstring
  AS '$libdir/postgis-1.5', 'BOX3D_extent_out'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE TYPE box3d_extent (
  alignment = double,
  internallength = 48,
  input = box3d_extent_in,
  output = box3d_extent_out
  );
   
  -- Availability: 1.4.0
  CREATE OR REPLACE FUNCTION box3d_extent(box3d_extent)
  RETURNS box3d
  AS '$libdir/postgis-1.5', 'BOX3D_extent_to_BOX3D'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION box2d(box3d_extent)
  RETURNS box2d
  AS '$libdir/postgis-1.5', 'BOX3D_to_BOX2DFLOAT4'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  CREATE OR REPLACE FUNCTION geometry(box3d_extent)
  RETURNS geometry
  AS '$libdir/postgis-1.5','BOX3D_to_LWGEOM'
  LANGUAGE 'C' IMMUTABLE STRICT;
   
  -- End of temporary hack
   
  -- Deprecation in 1.2.3
  CREATE OR REPLA