Select data from table to generate layer

[Umberto Nicoletti]

Henric,
first of all you need to make the mysql accessibile to mapserver by
converting coordintes into a valid mygis geometry.
Unfortunately there is not much documentation on mysql around, but I
could gather the following by looking at the source of mapmygis.c:


Standard ways of specifiying are :
(1) 'geometry_column from geometry_table'
(2) 'geometry_column from (<sub query>) as foo using unique
<column name> using SRID=<srid#>'",

NOTE: for (2) 'using unique' and 'SRID=' are optional, but its highly
recommended that you use them!!!

The most common problem with (1) is incorrectly uploading your data.
There must be an entry in the geometry_columns table.  This will be
automatically done if you used the shp2mysql program or created your
geometry column with the AddGeometryColumn() MYGIS function.

Another important thing to check is that the MYGIS user specified in
the CONNECTION string does have SELECT permissions on the table(s)
specified in your DATA string.

If you are using the (2) method (and have errors), you've probably made a typo.
Example:  'the_geom from (select the_geom,oid from mytable) as foo
using unique oid using SRID=76'
This is very much like the (1) example.  The subquery ('select
the_geom,oid from mytable') will be executed, and mapserver will use
'oid' (a postgresql system column) for uniquely specifying a geometry
(for mapserver queries).  The geometry (the_geom) must have a SRID of
76.

Example:
'roads from (select
table1.roads,table1.rd_segment_id,table2.rd_name,table2.rd_type from
table1,table2 where table1.rd_segment_id=table2.rd_segment_id) as foo
using unique rd_segment_id using SRID=89'

This is a more complex sub-query involving joining two tables.  The
resulting geometry (column 'roads') has SRID=89, and mapserver will
use rd_segment_id to uniquely identify a geometry.  The attributes
rd_type and rd_name are useable by other parts of mapserver.

To use a view, do something like:
'<geometry_column> from (SELECT * FROM <view>) as foo using unique
<column name> using SRID=<srid#>'

For example: 'the_geom from (SELECT * FROM myview) as foo using unique
gid using SRID=-1'

NOTE: for the (2) case, the ' as foo ' is requred.  The 'using unique
<column>' and 'using SRID=' are case sensitive.

Then you can build a simple form which either builds a filter or does
a query by attributes on the mysql layer.

HTH,
Umberto

On 9/28/07, Henric Ernstson <henric at ernstson.nu> wrote:
> HI I have made a simple application using PHP script, al data is in a database (mySQL).
>
> What I woud like to do now is to add a layer from the database, where the user can select what data to view.
>
> I got a tabble in my database with different clients/customer  reffering to geograpical coordinates. I would like to make a application / layer where i could select with client/customer to view on the map. Have any one some idee whare to start or where I could find an example?
>
> Best reg. Henric
>