postgis - Githubissues

sailei1 commented 1 year ago

CREATE TABLE testPolygon (
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Polygon, 4326)
);

select * from testPolygon

//矩形
INSERT INTO testPolygon (geom) VALUES (ST_GeomFromText('POLYGON((116.405285 39.913329, 116.405285 40.023826, 116.595874 40.023826, 116.595874 39.913329, 116.405285 39.913329))', 4326));
//圆形
INSERT INTO testPolygon (geom)
VALUES (ST_SetSRID(ST_Buffer(ST_MakePoint(80.64137923059108, 39.52936609122), 12.926648613653265), 4326));

//多边形  首尾点位相连
INSERT INTO testPolygon (geom) 
VALUES (ST_GeomFromText('POLYGON((42.46307204293636 29.594156804245564, 58.31615640768232 8.76741148988144, 92.0491735094836 23.003372130999875,42.46307204293636 29.594156804245564))', 4326));

create or replace function sector_3(lon float, lat float, azimuth float, distance integer, width integer)
returns geometry
language plpgsql
as
$$
declare
   sector geometry;
begin
    sector = ST_MakePolygon(ST_MakeLine(ARRAY[ST_SetSRID(ST_MakePoint(lon,lat),4326),
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth-(width/2))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth-(width/2-1*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth-(width/2-2*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth-(width/2-3*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth-(width/2-4*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth)/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth+(width/2-4*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth+(width/2-3*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth+(width/2-2*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth+(width/2-1*(width/2/5)))/180.0)::geometry,
                ST_Project(ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*(azimuth+(width/2))/180.0)::geometry,                                   
                ST_SetSRID(ST_MakePoint(lon,lat),4326)  
                     ]));
   return sector;
end;
$$;

select sector_3(38.945676,45.053163,90,120,40)

INSERT INTO testPolygon (geom)  
VALUES (sector_3(38.945676,45.053163,90,120,40))

INSERT INTO testPolygon (geom)  
VALUES (sector_3(69.241568,33.009880,70,22,10))

//扇形
INSERT INTO testPolygon (geom)
VALUES (ST_GeomFromText('POLYGON((97.40899094587365  21.337301364758986, 98.3473572885289  21.53531649848449, 99.27591760115587  21.75771858104706, 100.19354057604737  22.004236649767513, 101.0991082309575  22.274570360352314, 101.99151727119074  22.56839035281683, 102.86968043379666  22.88533865275973, 103.73252781223226  23.225029107499694, 104.5790081598784  23.587047856542913, 105.40809017082174  23.970953835808395, 106.21876373634227  24.37627931499659, 107.0100411755751  24.802530467446708, 107.78095843884742  25.24918797178845, 108.53057628222467  25.71570764465508, 109.25798141183448  26.201521103687064, 109.96228759657454  26.70603646001839, 110.64263674784887  27.228639039402022, 111.29819996501634  27.76869213109572, 111.92817854527864  28.32553776359606, 112.53180495677643  28.898497506275323, 113.10834377370888  29.486873295944747, 113.65709257233652  30.089948287337002, 114.1773827867765  30.706987726471585, 114.6685805235471  31.337239845839576, 115.13008733386926  31.979936780316443, 115.5613409427843  32.63429550268739, 90.29871565826775  41.55175649297438, 97.40899094587365  21.337301364758986, 97.40899094587365  21.337301364758986))',4326));

SELECT ST_X(geom) AS longitude, ST_Y(geom) AS latitude FROM points; //查询经纬度

sailei1 commented 1 year ago

CREATE TABLE points (id SERIAL PRIMARY KEY, geom GEOMETRY(Point, 4326));

INSERT INTO points (geom) VALUES (ST_SetSRID(ST_MakePoint(10, 20), 4326));

SELECT * FROM points WHERE ST_Intersects(geom, ST_SetSRID(ST_MakePoint(10, 20), 4326));

SELECT ST_X(geom) AS longitude, ST_Y(geom) AS latitude FROM points; //查询经纬度

select ST_X(location) AS longitude, ST_Y(location) AS latitude from points  inner join testpolygon
on ST_Within(ST_SetSRID(points.location,4326),ST_SetSRID(testpolygon.geom,4326))
where testpolygon.id=16

sailei1 commented 6 months ago

修改时区

select now(); show time zone set time zone "Asia/Shanghai"

sailei1 / blog

postgis #123