S
S
sha-man2020-01-20 09:43:39
PostgreSQL
sha-man, 2020-01-20 09:43:39

How to find geometric objects in a given area by coordinates?

Good afternoon, dear ones.
I am new to geo and postgis.
It is necessary to store objects with different geometry in the database (Postgres + PostGis), that is, there can be a point, a line, a polygon. Coord. in longitude and latitude format. As I understand it, one column can contain only one type of shape.
I created the table with the following query:
CREATE TABLE geom (
"id" serial primary key,
"name" varchar(255),
"type" smallint,
"pt" geometry(POINT, 4326),
"line" geometry(LINESTRING, 4326),
"polygon" geometry(POLYGON, 4326)
);
type - what type the record stores, and resp. in which column is the description of the object
typ=0, column pt - point,
typ=1,
typ=2, column polygon - polygon,
Questions:
1. Is it possible to store different types of geometries (point, line, polygon) in one column or is it correct to do it for different columns?
2. The task is to extract objects from the database that fall into the specified area. Let's say this is a square with known coordinates of the corners. How to find the hit of an object in a given area by known coordinates? With what functions? Is this function equally suitable for any type of object, or does it have its own functions for different geometries?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
F
freeExec, 2020-01-20
@freeExec

1. It is possible, but usually it is not practiced. Because often the semantics of the object already defines the geometry type.
postgis.net/workshops/postgis-intro/geometries.html
2. postgis.net/docs/manual-2.0/ST_Contains.html
postgis.net/docs/manual-2.0/ST_Within.html

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question