[Mapserver-dev] MySQL feature data joins
Smith, Michael ERDC-CRREL-NH
Michael.Smith at erdc.usace.army.mil
Thu Jun 17 09:47:12 EDT 2004
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C45471.4516C7D8
Content-Type: text/plain
You could use one of the spatial database connections (PostGIS, Oracle)
which would allow joins and thematic mapping. You would just have to move
your shape geometry into the database as well. The OGR tools make loading
data into Oracle (and I beleive PostGIS also) quite easy.
Mike Smith
GIS Specialist/Physical Scientist/Oracle Developer
Remote Sensing/GIS Center of Expertise
Army Engineer Research & Development Center
Hanover, NH
(603) 646-4765
michael.smith at erdc.usace.army.mil
-----Original Message-----
From: David Horowitz [mailto:dahoro at umd.edu]
Sent: Thursday, June 17, 2004 9:21 AM
To: steve.lime at dnr.state.mn.us
Cc: mapserver-dev at lists.gis.umn.edu
Subject: Re: Re: [Mapserver-dev] MySQL feature data joins
Thanks for your reply. The fact that it is impossible to use joined data to
classify a map causes major problems for my application. Using mapserver, I
generate maps of roads in my area which are colored based on current vehicle
speed information that my laboratory receives every 5 minutes from detectors
around the state.
Since I have been unable to classify my maps through the database directly,
my workaround solution has been to update a column in the corresponding DBF
file with the new speed data every five minutes. This is obviously not the
best solution, nor does it work with future goals for the application.
Any ideas on how to approach this problem? There must be some way to color
the roads in my maps based on data from the lab's database rather than from
the DBF file.
Thanks for you help, once again.
-----Original Message-----
From: "Steve Lime" <steve.lime at dnr.state.mn.us>
To: <woodbri at swoodbridge.com>, <dahoro at umd.edu>
Date: Wed, 16 Jun 2004 23:38:36 -0500
Subject: Re: [Mapserver-dev] MySQL feature data joins
Joins are query only, that is once you'd got your feature(s) then joins are
ok. You can't use joined data to classify a map. The reason is performance
(and it gets a bit complicated to code given the way layers work), joins can
be quite slow.
The alternative is to use MapScript (equally slow), perhaps OGR with point
data, use PostGIS, or pre-process your data a bit.
Sorry,
Steve
>>> Stephen Woodbridge <woodbri at swoodbridge.com> 06/16/04 2:47 PM >>>
David Horowitz wrote:
> Hello all,
>
> I'm writing an application which gets its spatial data from shapefiles
but its feature data from a SQL data source (either MySQL or Oracle). I
have read the Wiki topic "JoinDatasources" but did not find its explanation
of how to go about this sufficient. Any help here would be appreciated.
>
> To be more specific, I have all my data loaded into a MySQL database
(using the shp2mysql tool). Supposing the source shapefile is
"statemap.shp" and the feature column I wanted to access in my database was
called "title" what would my mapfile entry look like? Is using a join the
way to go about solving this problem?
I have a similar situation where all my spatial data is in shapefiles and I
have Summary File 1 data in MySQL. How would I set up a layer to to JOIN
MYSQL_ZIPCODE to DBF_ZIPCODE attributes and then specify MYSQL_INCOME as the
CLASSITEM so I can color polygons based on INCOME values from Summary File
1.
I read the docs on this but the details were not obvious and there was a
phrase about MYSQL JOINs only being supported in a query template which did
not make any sense to me.
-Steve
_______________________________________________
Mapserver-dev mailing list
Mapserver-dev at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-dev
_______________________________________________
Mapserver-dev mailing list
Mapserver-dev at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-dev
_______________________________________________
Mapserver-dev mailing list
Mapserver-dev at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-dev
------_=_NextPart_001_01C45471.4516C7D8
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2655.35">
<TITLE>RE: Re: [Mapserver-dev] MySQL feature data joins</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>You could use one of the spatial database connections =
(PostGIS, Oracle) which would allow joins and thematic mapping. You =
would just have to move your shape geometry into the database as well. =
The OGR tools make loading data into Oracle (and I beleive PostGIS =
also) quite easy.</FONT></P>
<P><FONT SIZE=3D2>Mike Smith</FONT>
<BR><FONT SIZE=3D2>GIS Specialist/Physical Scientist/Oracle =
Developer</FONT>
<BR><FONT SIZE=3D2>Remote Sensing/GIS Center of Expertise</FONT>
<BR><FONT SIZE=3D2>Army Engineer Research & Development Center =
</FONT>
<BR><FONT SIZE=3D2>Hanover, NH</FONT>
<BR><FONT SIZE=3D2>(603) 646-4765</FONT>
<BR><FONT SIZE=3D2>michael.smith at erdc.usace.army.mil</FONT>
<BR><FONT SIZE=3D2> </FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: David Horowitz [<A =
HREF=3D"mailto:dahoro at umd.edu">mailto:dahoro at umd.edu</A>] </FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, June 17, 2004 9:21 AM</FONT>
<BR><FONT SIZE=3D2>To: steve.lime at dnr.state.mn.us</FONT>
<BR><FONT SIZE=3D2>Cc: mapserver-dev at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Re: [Mapserver-dev] MySQL feature data =
joins</FONT>
</P>
<P><FONT SIZE=3D2>Thanks for your reply. The fact that it is =
impossible to use joined data to classify a map causes major problems =
for my application. Using mapserver, I generate maps of roads in =
my area which are colored based on current vehicle speed information =
that my laboratory receives every 5 minutes from detectors around the =
state.</FONT></P>
<P><FONT SIZE=3D2>Since I have been unable to classify my maps through =
the database directly, my workaround solution has been to update a =
column in the corresponding DBF file with the new speed data every five =
minutes. This is obviously not the best solution, nor does it =
work with future goals for the application.</FONT></P>
<P><FONT SIZE=3D2>Any ideas on how to approach this problem? =
There must be some way to color the roads in my maps based on data from =
the lab's database rather than from the DBF file.</FONT></P>
<P><FONT SIZE=3D2>Thanks for you help, once again.</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: "Steve Lime" =
<steve.lime at dnr.state.mn.us></FONT>
<BR><FONT SIZE=3D2>To: <woodbri at swoodbridge.com>, =
<dahoro at umd.edu></FONT>
<BR><FONT SIZE=3D2>Date: Wed, 16 Jun 2004 23:38:36 -0500</FONT>
<BR><FONT SIZE=3D2>Subject: Re: [Mapserver-dev] MySQL feature data =
joins</FONT>
</P>
<P><FONT SIZE=3D2>Joins are query only, that is once you'd got your =
feature(s) then joins are ok. You can't use joined data to classify a =
map. The reason is performance (and it gets a bit complicated to code =
given the way layers work), joins can be quite slow.</FONT></P>
<P><FONT SIZE=3D2>The alternative is to use MapScript (equally slow), =
perhaps OGR with point data, use PostGIS, or pre-process your data a =
bit.</FONT></P>
<P><FONT SIZE=3D2>Sorry,</FONT>
</P>
<P><FONT SIZE=3D2>Steve</FONT>
</P>
<P><FONT SIZE=3D2>>>> Stephen Woodbridge =
<woodbri at swoodbridge.com> 06/16/04 2:47 PM >>></FONT>
<BR><FONT SIZE=3D2>David Horowitz wrote:</FONT>
</P>
<P><FONT SIZE=3D2>> Hello all,</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> I'm writing an application which gets its =
spatial data from shapefiles</FONT>
<BR><FONT SIZE=3D2>but its feature data from a SQL data source (either =
MySQL or Oracle). I have read the Wiki topic =
"JoinDatasources" but did not find its explanation of how to =
go about this sufficient. Any help here would be =
appreciated.</FONT></P>
<P><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> To be more specific, I have all my data loaded =
into a MySQL database</FONT>
<BR><FONT SIZE=3D2>(using the shp2mysql tool). Supposing the =
source shapefile is "statemap.shp" and the feature column I =
wanted to access in my database was called "title" what would =
my mapfile entry look like? Is using a join the way to go about =
solving this problem?</FONT></P>
<P><FONT SIZE=3D2>I have a similar situation where all my spatial data =
is in shapefiles and I have Summary File 1 data in MySQL. How would I =
set up a layer to to JOIN MYSQL_ZIPCODE to DBF_ZIPCODE attributes and =
then specify MYSQL_INCOME as the CLASSITEM so I can color polygons =
based on INCOME values from Summary File 1.</FONT></P>
<P><FONT SIZE=3D2>I read the docs on this but the details were not =
obvious and there was a</FONT>
</P>
<P><FONT SIZE=3D2>phrase about MYSQL JOINs only being supported in a =
query template which did not make any sense to me.</FONT>
</P>
<P><FONT SIZE=3D2>-Steve</FONT>
</P>
<P><FONT =
SIZE=3D2>_______________________________________________</FONT>
<BR><FONT SIZE=3D2>Mapserver-dev mailing list</FONT>
<BR><FONT SIZE=3D2>Mapserver-dev at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2><A =
HREF=3D"http://lists.gis.umn.edu/mailman/listinfo/mapserver-dev" =
TARGET=3D"_blank">http://lists.gis.umn.edu/mailman/listinfo/mapserver-de=
v</A></FONT>
</P>
<P><FONT =
SIZE=3D2>_______________________________________________</FONT>
<BR><FONT SIZE=3D2>Mapserver-dev mailing list</FONT>
<BR><FONT SIZE=3D2>Mapserver-dev at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2><A =
HREF=3D"http://lists.gis.umn.edu/mailman/listinfo/mapserver-dev" =
TARGET=3D"_blank">http://lists.gis.umn.edu/mailman/listinfo/mapserver-de=
v</A></FONT>
</P>
<BR>
<BR>
<BR>
<BR>
<BR>
<P><FONT =
SIZE=3D2>_______________________________________________</FONT>
<BR><FONT SIZE=3D2>Mapserver-dev mailing list</FONT>
<BR><FONT SIZE=3D2>Mapserver-dev at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2><A =
HREF=3D"http://lists.gis.umn.edu/mailman/listinfo/mapserver-dev" =
TARGET=3D"_blank">http://lists.gis.umn.edu/mailman/listinfo/mapserver-de=
v</A></FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C45471.4516C7D8--
More information about the mapserver-dev
mailing list