Intersections
Bob Basques
bob.basques at CI.STPAUL.MN.US
Thu Oct 14 17:33:28 EDT 2004
Eric Katherman wrote:
>The linetypes are LINE and I just started playing with postgis and have
>successfully been able to draw the centerlines from the DB if that helps at
>all. I am not sure if the line segments are city blocks or not, I got the
>data from my county.
>
>Here are two lines from ogrinfo.
>
>
This is very similar to what I started with for our centerline file.
The way I handle the intersections is to create a new SQL table from the
Segment data. In my dataset though, I have the Names of the street as
a text field. It also has the FROM and TO street names in it as well.
With these three piece of data a nd the endpoint coordinates, one can
assemble a new TABLE with a self join SQL request that builds a point
database with the name of each street name that ends at each
corrosponding line segment or SuperNODE (SNODE). Note: this will get
all street names at every intersectionn, not just those that cross each
other. Notice further down, that DALE, COMO and FRONT all intersect each
other at the same intersection.
The end results look something like this?
SQL> desc snodes;
Name Null? Type
----------------------------------------- --------
----------------------------
ID CHAR(9)
X NUMBER(6)
Y NUMBER(6)
INTERS CHAR(100)
SQL> select * from snodes where rownum < 5;
ID X Y
--------- ---------- ----------
INTERS
--------------------------------------------------------------------------------
301000001 546326 171797
COMO AV and HUNTING VALLEY RD
301000003 546801 171503
COMO AV and EUSTIS ST
301000004 548524 171520
ID X Y
--------- ---------- ----------
INTERS
--------------------------------------------------------------------------------
BRANSTON ST
301000005 548959 171546
DUDLEY AV and GRANTHAM ST
SQL> select * from snodes where inters like '%COMO%' and inters like
'%FRONT%';
ID X Y
--------- ---------- ----------
INTERS
--------------------------------------------------------------------------------
312000057 564421 165392
COMO PL and FRONT AV
901500906 566512 165392
COMO AV and FRONT AV and N DALE ST
----------------------------------------------------
The create statement (for Oracle, your's will be different I'm sure)
would look something like this:
create view snode as
select fnode_ snode, streetname, cityrigt, cityleft, x1, y1, z1
from tlg_county
union
select tnode_, streetname, cityrigt, cityleft, x2, y2, z2
from tlg_county
Your data will obviously be different, but hopefully this helps you in
the right direction.
Once you have a SNODE file then you can just add it as another layer
behind MapServ that can be selected via the StreetNames or via an ID
from some other process, we use a PERL CGI for this against the
database, and then zoom to the spot with MapServ, but it could all just
be done inside of MapServ too.
---------------------------------------------------
The TLG_COUNTY table referenced above looks like this:
SQL> desc tlg_county
Name Null? Type
----------------------------------------------------- --------
------------------------------------
GEOM
MDSYS.SDO_GEOMETRY
FNODE_ FLOAT(126)
TNODE_ FLOAT(126)
LPOLY_ FLOAT(126)
RPOLY_ FLOAT(126)
LENGTH FLOAT(126)
TLGRDS_ FLOAT(126)
TLGRDS_ID FLOAT(126)
L_F_ADD FLOAT(126)
R_F_ADD FLOAT(126)
L_T_ADD FLOAT(126)
R_T_ADD FLOAT(126)
STREETALL VARCHAR2(35)
F_CLASS VARCHAR2(6)
ALT_NAM1 VARCHAR2(36)
ALT_NAM2 VARCHAR2(36)
CENCTY_L FLOAT(126)
CENMUN_L FLOAT(126)
CENCTY_R FLOAT(126)
CENMUN_R FLOAT(126)
ZIP5_L FLOAT(126)
ZIP5_R FLOAT(126)
CITYLEFT VARCHAR2(20)
CITYRIGT VARCHAR2(20)
PREDIR VARCHAR2(2)
STREETNAME VARCHAR2(20)
TYPE VARCHAR2(8)
SUFDIR VARCHAR2(2)
CTYLABRV VARCHAR2(3)
CTYRABRV VARCHAR2(3)
SPEED_LIM FLOAT(126)
ONEWAY FLOAT(126)
TLGID FLOAT(126)
X1 FLOAT(126)
Y1 FLOAT(126)
Z1 FLOAT(126)
X2 FLOAT(126)
Y2 FLOAT(126)
Z2 FLOAT(126)
bobb
>
>OGRFeature(Streets):4
> OBJECTID (Integer) = 13
> LENGTH (Real) = 449.92674416700
> ADD1_L (Integer) = 0
> ADD1_R (Integer) = 0
> ADD2_L (Integer) = 0
> ADD2_R (Integer) = 0
> DATE_ (Integer) = 19970705
> SOURCE (Integer) = 2
> MAP (Integer) = 8
> COMMUNITY (String) = Maricopa County
> TYPE (String) = (null)
> NAME (String) = Apartment
> LINESTRING (459403.220 1075050.365,459434.741 1074948.799,459520.438
>1074768.288,459596.252 1074646.142)
>
>OGRFeature(Streets):5
> OBJECTID (Integer) = 14
> LENGTH (Real) = 787.59510637000
> ADD1_L (Integer) = 0
> ADD1_R (Integer) = 0
> ADD2_L (Integer) = 0
> ADD2_R (Integer) = 0
> DATE_ (Integer) = 19970705
> SOURCE (Integer) = 2
> MAP (Integer) = 8
> COMMUNITY (String) = Maricopa County
> TYPE (String) = (null)
> NAME (String) = Apartment
> LINESTRING (459382.095 1075094.728,459287.050 1075056.376,459285.226
>1074952.446,459294.343 1074888.628,459478.501 1074564
>.073,459596.252 1074646.142)
>
>
>
>>What does your Street centerline data look like now?
>>
>>Is it a seperate line segment per city block or polylines or ???
>>
>>bobb
>>
>>Eric Katherman wrote:
>>
>>
>>>Can someone please give me some idea or a sample on how one would begin to
>>>find an intersection of two lines, streets in my case. Info on finding
>>>intersecting lines with other data besides streets could be helpful as
>>>well. I ultimately want to give a user the option to enter two street
>>>names and then zoom to that intersection. Any suggestions or examples?
>>>
>>>Eric
>>>
>>>
>
>
>
>
More information about the mapserver-users
mailing list