- alikilic9
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.
Users.sql file It is the people who live in the building and walk by the roadside.
There are building polygons in the Building.sql file.
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