postgresql 空间函数问题

A表(面信息),B表(点信息),

create table A
(
intGridID int,
vcPolygon varchar
);

INSERT INTO A ("intGridId", "vcPolygon") VALUES ('3', 'POLYGON(113.070104 22.554866,113.068593 22.554933,113.068593 22.556933,113.070104 22.556866000000003,113.070104 22.554866)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('7', 'POLYGON(113.08773500000001 22.552801137268283,113.08913500000001 22.553302274536566,113.08913500000001 22.55530227453657,113.08773500000001 22.554801137268285,113.08773500000001 22.552801137268283)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('11', 'POLYGON(113.09333500000002 22.554805686341417,113.09473500000003 22.5553068236097,113.09473500000003 22.557306823609704,113.09333500000002 22.55680568634142,113.09333500000002 22.554805686341417)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('15', 'POLYGON(113.09893500000004 22.556810235414552,113.10033500000004 22.557311372682836,113.10033500000004 22.55931137268284,113.09893500000004 22.558810235414555,113.09893500000004 22.556810235414552)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('19', 'POLYGON(113.10489899999999 22.555635,113.10553074809283 22.554335,113.10753074809284 22.554335,113.106899 22.555635,113.10489899999999 22.555635)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('23', 'POLYGON(113.10742599237136 22.550434999999997,113.10789299999999 22.549474,113.109893 22.549474,113.10942599237137 22.550434999999997,113.10742599237136 22.550434999999997)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('27', 'POLYGON(113.10269300000002 22.55081956174061,113.10119300000002 22.550422415654147,113.10119300000002 22.55242241565415,113.10269300000002 22.552819561740613,113.10269300000002 22.55081956174061)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('31', 'POLYGON(113.09669300000004 22.549230977394757,113.09519300000005 22.548833831308293,113.09519300000005 22.550833831308296,113.09669300000004 22.55123097739476,113.09669300000004 22.549230977394757)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('35', 'POLYGON(113.091705 22.547957999999998,113.090133 22.547911,113.090133 22.549911,113.091705 22.549958,113.091705 22.547957999999998)');
INSERT INTO A ("intGridId", "vcPolygon") VALUES ('39', 'POLYGON(113.078869 22.553369999999997,113.080069 22.55253403308112,113.080069 22.554534033081122,113.078869 22.55537,113.078869 22.553369999999997)');

create table B
(
bisessid varchar,
flong float,
flat float
);

INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017010701684961', '113.084480000', '22.625615000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017045061428887', '113.136088000', '22.596158000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017045061428887', '113.136088000', '22.596158000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017045061428887', '113.136088000', '22.596158000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017045061459407', '113.073557000', '22.619555000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017045061459407', '113.073557000', '22.619555000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017066536253079', '113.072613320', '22.556025360');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017066536253079', '113.072613320', '22.556025360');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017122370830130', '113.076132000', '22.619137000');
INSERT INTO B ("bisessid", "flong", "flat") VALUES ('2793017126665847959', '113.104918090', '22.690929220');

现在要计算,B表的point(flong,flat) 存在于A表的哪个面里面,生成一张表叫C

create table C
(
bisessid varchar,
flong float,
flat float,
intGridId int
);

现有SQL : ST_Contains(ST_MakePolygon(ST_GeomFromText('LINESTRING('||replace(replace(tt1."vcPolygon",'POLYGON(',''),')','')||')')) ,
st_point(tt2."flong",tt2."flat")) 判断点是否在面里面,返回 t/f
,求大神指导,A表数据大概1000条,B表数据40W+
麻烦大神指导指导谢谢

1个回答

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐