# Databases : PostgreSQL : PostGIS : Cheatsheet ## Types TODO ## Using Types #### Points ```sql geometry(Point,4326) -- Point column type '0101000020E6100000038DD73571BC3D40EA687B0455E457C0' -- example of returned raw value ST_AsText( pt ) -- convert to text; ex: SELECT ST_AsText( pt ) FROM t -> 'POINT(33.1694453 -87.5276732)' ST_X( pt ) -- extract lat float; ex: SELECT ST_X( pt ) FROM t -> 33.1694453 ST_Y( pt ) -- extract lng float; ex: SELECT ST_Y( pt ) FROM t -> -87.5276732 ST_GeomFromText( 'POINT(0 0)', 4326 ) -- convert text to point type ST_Point( 33.1694453, -87.5276732, 4326 ) -- convert floats to point type ``` ## Built-in Schemas, Tables, Views ### Schemas ``` tiger tiger_data topology ``` ### In "public" Schema ```sql SELECT count( * ) FROM spatial_ref_sys -> 8500 SELECT count( * ) FROM geography_columns -> 0 SELECT count( * ) FROM geometry_columns -> 12 ``` #### Table: spatial_ref_sys ```sql Column | Type | Collation | Nullable | Default -----------+-------------------------+-----------+----------+--------- srid | integer | | not null | auth_name | character varying(256) | | | auth_srid | integer | | | srtext | character varying(2048) | | | proj4text | character varying(2048) | | | Indexes: "spatial_ref_sys_pkey" PRIMARY KEY, btree (srid) Check constraints: "spatial_ref_sys_srid_check" CHECK (srid > 0 AND srid <= 998999) ``` #### View: geography_columns ```sql View "public.geography_columns" Column | Type | Collation | Nullable | Default --------------------+---------+-----------+----------+--------- f_table_catalog | name | | | f_table_schema | name | | | f_table_name | name | | | f_geography_column | name | | | coord_dimension | integer | | | srid | integer | | | type | text | | | ``` #### View: geometry_columns ```sql View "public.geometry_columns" Column | Type | Collation | Nullable | Default -------------------+------------------------+-----------+----------+--------- f_table_catalog | character varying(256) | C | | f_table_schema | name | | | f_table_name | name | | | f_geometry_column | name | | | coord_dimension | integer | | | srid | integer | | | type | character varying(30) | | | SELECT * FROM geometry_columns f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type -----------------+----------------+--------------+-------------------+-----------------+------+----------------- mydb | tiger | county | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | state | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | place | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | cousub | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | edges | the_geom | 2 | 4269 | MULTILINESTRING mydb | tiger | addrfeat | the_geom | 2 | 4269 | LINESTRING mydb | tiger | faces | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | zcta5 | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | tabblock20 | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | tract | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | tabblock | the_geom | 2 | 4269 | MULTIPOLYGON mydb | tiger | bg | the_geom | 2 | 4269 | MULTIPOLYGON ``` If you create a table with a `geometry(Point,4326)` column... ```sql SELECT count( * ) FROM geometry_columns -> 13 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type -----------------+----------------+--------------+-------------------+-----------------+------+----------------- ...12 default rows... mydb | public | foo | pt1 | 2 | 4326 | POINT ```