Heinz Heinz - 1 month ago 11
SQL Question

SQL: How to get source/target of given coordinates in a line shapefile in PostGIS table?

I loaded a line shapefile in PostGIS and used

pgr_createTopology
in pgRouting to make the table routable by adding source and target (assign 2 end nodes of the line source number and target number). The following figure was part of the table:

enter image description here

Now I have some coordinates of end nodes, which belong to lines in the table, and I want to know that what number of sources/targets were corresponding to these coordinates.

For example, in the table above, suppose that
259463.392, 2737830.062
was one of end nodes of
line id=1
and
line id=2
, then this coordinate has
source/target=175


I am newbie to SQL and tried some query but got errors:

SELECT ST_AsText(geom) from source;
FROM public.tc_line15_split;


error:

ERROR: syntax error at or near "FROM"
LINE 2: FROM public.tc_line15_split;
^
********** Error **********

ERROR: syntax error at or near "FROM"
SQL state: 42601
Character: 45





UPDATE#1

I am thinking I could just know what I want if column source/target contain information of node coordinate, but seems not, they are just column containing numbers.

I got the vertices table as below:

enter image description here

I used the following query to get the table below:

select source, target, st_astext(geom) as geom from public.tc_line15_split;


enter image description here

and I am still looking for if I could get my need through the 2 tables above.

So I tried the query below and got 2 lines near the given coordinate:

select id from tc_line15_split
where st_dwithin(geom, st_setsrid(st_makepoint(259463.392, 2737830.062), 3826), 0.1);


enter image description here

and later I found from the table showed in the first figure that the coordinate is source/target=54 of id 170/51, respectively, but it's still inefficient.

I am wondering that is there ways to find the same source/target number, which in this case
line id=51
and
line id=170
both contain, after I found the given coordinate is located between these two lines?




UPDATE#2

Based on the vertices table, I used the following query to get corresponding source number, which is also point id, of the given coordinate:

select id from tc_line15_split_vertices_pgr
where st_dwithin(the_geom, st_setsrid(st_makepoint(259463.392, 2737830.062), 3826), 0.1);


enter image description here

Answer

If what you are trying to achieve is to figure out how many of the network edges share a common node, then the easiest way to achieve this is to use the vertices table generated by pgr_createTopology(). Per the documentation, it generates a table named the same as your edges table but with _vertices_pgr appended.

If you then run the pgr_analyzeGraph() method, it will populate the empty columns inside the my_table_vertices_pgr table with statistics of each vertex. One of these statistics is the cnt column, which shows the number of times a particular vertex is shared by any adjoining edges.

enter image description here

Comments