Search code examples
sqlpostgresqlpostgispgrouting

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

Solution

  • 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

    Edit:

    Regarding some of the other aspects of your question:

    The first query in your question returns an error because of syntax, this should work:

    SELECT ST_AsText(geom) FROM public.tc_line15_split;

    Regarding Update #1 -> The node coordinates aren't stored explicitly in the vertices table, but you can retrieve them for a specific node id (e.g. 15) by using this query:

    SELECT ST_AsText(ST_Centroid(the_geom))
    FROM tc_line15_split_vertices_pgr
    WHERE id = 15

    Or, if you simply want X and Y:

    SELECT ST_X(the_geom), ST_Y(the_geom)
    FROM tc_line15_split_vertices_pgr
    WHERE id = 15