postgresql服务器编程——计算最近点及线段分割

概述drop function if exists processStationPoint(); create function processStationPoint() returns text as $$ declare v_stationId integer; v_stationGeom geometry; v_startPoint geometry; v_stationSource inte drop function if exists processstationPoint(); create function processstationPoint() returns text as $$ declare v_stationID integer; v_stationGeom geometry; v_startPoint geometry; v_stationSource integer; v_stationTarget integer; v_stationline geometry; v_stationlineID integer; v_stationSrcpulloutable boolean; v_stationDstPulloutable boolean; v_searchNode geometry; v_searchNodeID integer; v_searchline geometry; v_searchlineMerged geometry; v_searchlineID integer; v_searchlineOneway varchar; v_searchlinesrc integer; v_searchlineDst integer; v_searchlinesrcIn integer; v_searchlinesrcOut integer; v_searchlineDstIn integer; v_searchlineDstOut integer; v_srcsegline geometry; v_dstsegline geometry; v_curs cursor for select gID,geom from station order by gID asc; begin v_stationSrcpulloutable := false; v_stationDstPulloutable := false; v_searchNodeID := -1; drop table if exists stationNear; CREATE table stationNear ( stationID integer,geom geometry(Point),CONSTRAINT "stationNear_pkey" PRIMARY KEY (stationID) ); open v_curs; loop fetch v_curs into v_stationID,v_stationGeom; --raise notice 'fetch v_curs into %,%',v_stationID,v_stationGeom; if not found then exit;end if; /* v_searchNode := null; select ID,the_geom into v_searchNodeID,v_searchNode from changsharoad_connected_vertices_pgr where ST_distance(the_geom,v_stationGeom) < 0.000001; if (v_searchNode != null) then update station set isNode = true where gID = v_stationID; update station set nodeID = v_searchNodeID where gID = v_stationID; end if;*/ v_searchline := null; select gID,geom,oneway,source,target into v_searchlineID,v_searchline,v_searchlineOneway,v_searchlinesrc,v_searchlineDst from changsharoad_connected where ST_DWithin(geom::geography,v_stationGeom::geography,1000,true) order by ST_distance(geom,v_stationGeom) limit 1; if (v_searchline is null) then --raise notice '%,v_searchline is null',v_stationID; else v_startPoint := ST_ClosestPoint(v_searchline,v_stationGeom); INSERT INTO stationNear(stationID,geom) VALUES(v_stationID,v_startPoint) ON conflict(stationID) DO update set stationID = v_stationID,geom = v_startPoint; raise notice 'ST_ClosestPoint is on v_searchline: %',ST_distanceSphere(v_searchline,v_stationGeom) < 0.1; select ein,eout into v_searchlinesrcIn,v_searchlinesrcOut from changsharoad_connected_vertices_pgr where ID = v_searchlinesrc; select ein,eout into v_searchlineDstIn,v_searchlineDstOut from changsharoad_connected_vertices_pgr where ID = v_searchlineDst; if (v_searchlineOneway != 'T' and v_searchlineDstOut > 0) then v_stationDstPulloutable := true; --raise notice '%,v_stationPulloutable is true',v_stationID; else --raise notice '%,v_stationPulloutable is false',v_stationID; end if; if (v_searchlineOneway != 'F' and v_searchlinesrcOut > 0) then v_stationSrcpulloutable := true; --raise notice '%,v_stationID; end if; v_searchlineMerged := ST_lineMerge(v_searchline); declare v_lineFraction float; v_isNode boolean; v_nodeID integer; begin v_nodeID := -1; v_lineFraction := ST_lineLocatePoint(v_searchlineMerged,v_startPoint); if (v_lineFraction < 0.0001) then v_srcsegline = null; v_isNode := true; v_nodeID := v_searchlinesrc; v_lineFraction := 0; --raise notice '坐标跟起点相同'; else v_srcsegline := st_reverse(st_linesubstring(v_searchlineMerged,v_lineFraction)); end if; if ((1.0 - v_lineFraction) < 0.0001) then v_dstsegline = null; v_isNode := true; v_nodeID := v_searchlineDst; v_lineFraction := 1; --raise notice '坐标跟终点相同'; else v_dstsegline := st_linesubstring(v_searchlineMerged,v_lineFraction,1); end if; update station set lineID = v_searchlineID,startPoint = v_startPoint,isnode = v_isNode,nodeID = v_nodeID,source = v_searchlinesrc,lineFraction = v_lineFraction,target = v_searchlineDst,srcpulloutable = v_stationSrcpulloutable,dstPulloutable = v_stationDstPulloutable,srcsegline = v_srcsegline,dstsegline = v_dstsegline where gID = v_stationID; --raise notice 'line geometry: %,st_astext(v_srcsegline),st_astext(v_dstsegline); --raise notice '%,update station.... ok!',v_stationID; end; end if; end loop; close v_curs; return 'ok'; end; $$ language plpgsql; 总结

以上是内存溢出为你收集整理的postgresql服务器编程——计算最近点及线段分割全部内容，希望文章能够帮你解决postgresql服务器编程——计算最近点及线段分割所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。