[Mapserver-dev] MySQL feature data joins

[Stephen Woodbridge]

David,

According to Steve's reply I think the only way to do this is to move 
ALL the data in PostGIS. I think this is a serious problem for thematic 
mapping. For example, I have 18 GB of spatial vector data in shapefiles 
and 103 GB of Summary file 1 data in MySQL and moving all this into 
PostGIS seems like a HUGE problem. this might not be as big a problem 
for you.

I think that part of the problem in the code is that for each object 
being drawn, you would have to make separate query to the mySQL database 
in order to classify the object. I for one would be interested in being 
able to do this even at a performance hit. I know moving to PostGIS will 
be slower than shapefiles to start with.

On second thought, I think you would not need to move ALL your data but 
only the layers you want to classify with a join. I still am not happy 
with the prospect of having to learn, move data to and administer 
another database.

-Steve

David Horowitz wrote:

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