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

Question

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:

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:

I used the following query to get the table below:

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

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);

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);

Originally I need to use QGIS to find the location of the coordinate and what line (with id) it belong, and then use the table in the post to verify what source/target the coordinate correspond. It is too inefficient. So I just want the process to be much more quick and efficient, and I think it may be achieved. — Heinz, Nov 02 '16 at 13:38
Pg_createtopology also creates a vertices table that may already contain the information that you are looking for? — songololo, Nov 02 '16 at 14:59
@shongololo I got the vertices table `tc_line15_split_pgr`, but it only contains id, cnt, chk and geometry, no details about number of source/target. — Heinz, Nov 03 '16 at 09:37
`cnt` is the number of edges that have a source / target at that vertex. — songololo, Nov 03 '16 at 10:11

score 1 · Answer 1 · answered Nov 02 '16 at 13:55

To list out the table:

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

You should also have a vertices table with columns like id and geom and if you want to find the nearest node to a location you can used something like:

select id from vertices where st_dwithin(geom, st_setsrid(st_makepoint(x,y), 3826), tol);

where x,y are you coordinates and tol is a search radius.

songololo · Accepted Answer · 2016-11-03T14:35:33.350

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.

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

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

2 Answers2

Edit: