Intersections
Bob Basques
bob.basques at CI.STPAUL.MN.US
Thu Oct 14 18:50:18 EDT 2004
Eric Katherman wrote:
>My streets table contains the following fields which contain basically
>everything but the to and from name.
>
>gid INT4
>objectid INT8
>length NUMERIC (e.g. 208.98332212)
>add1_l INT8 (e.g. 27656)
>add1_r INT8 (e.g. 27657)
>add2_l INT8 (e.g. 27720)
>add2_r INT8 (e.g. 27721)
>date_ VARCHAR
>source INT4
>map INT4
>community VARCHAR
>type VARCHAR (e.g. Rd)
>name VARCHAR (the street name)
>the_geom GEOMETRY
>
>Unfortunately my table doesn't have as much detail as your's appears to,
>although with what I see, in theroy I should be able to create an "snode"
>table from where any point in the "the_geom" table matches another and then
>get the names from them right? If Central and 5th intersect then there would
>be a record for both Central and 5th that should have that corresponding
>point in it.
>
>
that's the idea. then you let the user figure out which record they
want to use. We even do some generalizing after the select by grouping
all location less than 150 feet from each other into a single point.
This works well for those spots that have two raodways crossing each
other in both direction, wher typically you would get back four
different intersection points.
>The problem that I had when trying to test a select statement using the_geom
>table returned this:
>
>SQL>select name from streets where the_geom LIKE '475151.296';
>ERROR:operator does not exist: geometry ~~ "unknown"
>HINT:No operator matches the given name and argument type(s). You may need to
>add explicit type casts.
>
>
Are the numbers stored as Ascii or actual numbers? If the GEOMETRY is
anything like ORACLE, it's a bit harder to get at it directly with a SQL
select. You may need a procedure of sort to get at the actual values.
bobb
>Eric
>
>
>
>>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