How to query Postgis database to pull all user coordinates from one table, within a polygon from another

huangapple go评论64阅读模式
英文:

How to query Postgis database to pull all user coordinates from one table, within a polygon from another

问题

我有一个带有多边形的Postgis数据库,存储在geom::geometry列中。我有一个包含坐标的'type'列的表。我想要提取所有包含在特定多边形内的用户坐标行。

我已经在Stack Overflow 上进行了几天的研究。我尝试了各种查询(ST_Contains、ST_Within),其中大多数告诉我一个我知道存在的函数不存在。我认为它被我提供的坐标点所混淆,想知道点列应该使用什么格式或类型。

以下是我尝试的查询,以便让你了解我的需求...

select u.user_id 
from electorial_boundries as e 
INNER JOIN user_coordinates as u on
   ST_Contains(e.geom,ST_GeomFromText('SRID=4326;POINT(u.user_coor)')) 
where e.org_id = 595

////另外如请求所示

期望的结果:

列出所有在选定多边形中的用户坐标中出现的行。

以下是表格的SQL代码...(抱歉,我不太熟悉如何使用fiddle)

CREATE TABLE public.user_coordinates
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id integer NOT NULL,
    user_coor point NOT NULL,
    CONSTRAINT user_coordinates_pkey PRIMARY KEY (id)
)

CREATE TABLE public.electorial_boundries
(
    id integer NOT NULL DEFAULT nextval('electorial_boundries_id_seq'::regclass),
    data_id bigint,
    geom geometry(MultiPolygon,4326),
    district_name character(120) COLLATE pg_catalog."default",
    CONSTRAINT electorial_boundries_pkey PRIMARY KEY (id)
)

错误是我收到"函数(ST_Contains或ST_Within)不存在"的消息。但似乎是由于我试图提供的点引起的,因为在u.user_coor的位置放置实际坐标会产生一些结果,尽管不仅限于多边形内的用户。

英文:

I have a Postgis database with polygons in a geom::geometry column. I have table with a 'point' type column of coordinates. I want to pull all of the user_coordinates rows where their coordinate is found within a specific polygon.

I've been researching answers on stackoverflow for days. I've tried all sorts of queries (ST_Contains, ST_Within), most of them telling me that a function I know exists, doesn't. I think it's being thrown off by the point I'm giving it, and wonder what format or type the point column should be in.

Here's a query I'm attempting to give you an idea of what I'm after...

select u.user_id 
from electorial_boundries as e 
INNER JOIN user_coordinates as u on
   ST_Contains(e.geom,ST_GeomFromText('SRID=4326;POINT(u.user_coor))) 
where e.org_id = 595

////Additionally as requested

What is expect:

List all the rows from user_coordinates where the coordinate in user_coordinates.user_coor shows up in the selected polygon from electorial_boundries.geom

Here is the sql for the tables...(sorry, I'm not familiar with how best to use fiddle)

CREATE TABLE public.user_coordinates
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id integer NOT NULL,
    user_coor point NOT NULL,
    CONSTRAINT user_coordinates_pkey PRIMARY KEY (id)
)

CREATE TABLE public.electorial_boundries
(
    id integer NOT NULL DEFAULT nextval('electorial_boundries_id_seq'::regclass),
    data_id bigint,
    geom geometry(MultiPolygon,4326),
    district_name character(120) COLLATE pg_catalog."default",
    CONSTRAINT electorial_boundries_pkey PRIMARY KEY (id)
)

The errors are I get that "Function (ST_Contains or ST_Within) Does not exist". But it seems it's just conflicted by the point I'm trying to give it, as an actual coordinate in place of u.user_coor yields some results, though not limited to users in the polygon.

答案1

得分: 0

The column user_coor is of type point, which is a native postgres type that is not directly usable with PostGIS functions.

The error message likely tells you that function ST_Contains(geometry, point) does not exist, which highlights the wrong "point" type as the function exists for two geometries only.

If you can, you should change the column definition to be of type geometry(point,4326).

You can however cast the point to a geometry

select u.user_id 
from electorial_boundries as e 
INNER JOIN user_coordinates as u on
   ST_Contains(e.geom, u.user_coor::geometry) 
where e.org_id = 595
英文:

The column user_coor is of type point, which is a native postgres type that is not directly usable with PostGIS functions.

The error message likely tells you that function ST_Contains(geometry, point) does not exist, which highlights the wrong "point" type as the function exists for two geometries only.

If you can, you should change the column definition to be of type geometry(point,4326).

You can however cast the point to a geometry

select u.user_id 
from electorial_boundries as e 
INNER JOIN user_coordinates as u on
   ST_Contains(e.geom, u.user_coor::geometry) 
where e.org_id = 595

huangapple
  • 本文由 发表于 2023年6月1日 01:48:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76376107.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定