Search
  • alikilic9
    • Nov 27, 2021
    • 2 min read

Spatial Queries and Functions - PostGIS Vol.1

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.


spatial relation data
.zip
Download ZIP • 14KB

  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

Tabloların Haritalandırılması

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


2-Creating a Spatial Table

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



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.


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.


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


111 views