Spatial Queries and Functions – PostGIS Vol.1

Article Number : 17

I want to add spatial database queries that I use very often here. After all, I write my queries by searching directly on google rather than memorizing. Then if I edit sql query around and somehow get the right result, there is no issue.

I’ve attached a zip file below so you can test the spatial relationships. Please download this file and add it to your test database

Download spatial relation data

1. Users.sql file It is the people who live in the building and walk by the roadside.
2. There are building polygons in the Building.sql file.
3. Street.sql file contains LineString data of streets.

Fields of Tables

Users Table Fields

• id : integer
• name : string
• geom : geometry Point

Streets Table Fields

• id : integer
• name : string
• geom : geometry LineString

Buildings Table Fields

• id : integer
• name : string
• type : string
• price : string
• floor : string
• geom : geometry Polygon

Mapping DB Tables

There are also geojson files in the downloaded file. You can easily view these files by dragging and dropping them onto the GISLayer Web Editor. When you do this, you will get a view similar to the one below. You can change the basemap and the color of the geometries as you wish.

Queries

1 – Installing PostGIS Plugins

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

2-Creating a Spatial Table

Most used geometry types POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT,
geom GEOGRAPHY(POINT,4326));

3 – Adding a Geometric Column to a Table with AddGeometryColumn Function

The parameters of this function are the table name, the name of the geometry column you want to add, the SRID number of the projection you will use, the geometry type and the size. If you are going to store a three-dimensional geometry, this parameter value should be 3.

SELECT AddGeometryColumn('TABLE_NAME', 'GEOMETRY_COLUMN_NAME', 4326, 'LINESTRING', 2);

4 – Deleting a Geometric Column in a Table with DropGeometryColumn Function

The parameters of this function are, in order, the schema name, the table name, the name of the column containing the geometry. This information is sufficient to delete.

SELECT DropGeometryColumn ('SCHEMA_NAME','TABLE_NAME','GEOMETRY_COLUMN_NAME');

5 – Using ST_GeomFromText – Adding WKT Geometry to Table

ST_GeomFromText(‘WKT’,SRID) takes the first parameter of this function WKT as a string in single quotes. The second parameter is the SRID, EPSG code of the coordinates that exist in the WKT. For example, if you are using latitude longitude, you can use the value 4326. For Detail

INSERT INTO users (id,name,geom) VALUES 
(NEXTVAL('users_seq'),'New User',ST_GeomFromText('POINT(39.92387 32.81830)',4326));