Heinz - 7 months ago 44

SQL Question

I loaded a line shapefile in PostGIS and used

`pgr_createTopology`

Now I have some

For example, in the table above, suppose that

`259463.392, 2737830.062`

`line id=1`

`line id=2`

`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

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`

`line id=170`

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

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.

Source (Stackoverflow)