[Qgis-user] QGIS -> MySQL -> Mapserver ??

Saber Razmjooei razmjooeis at faunalia.co.uk
Wed Jul 27 07:03:32 PDT 2011 

Chris
Have you checked this:

18.4.2.1. Creating Geometry Values Using WKT Functions
http://www.qijoo.com/FAPM/mysql/5.1/MySQL_51_en/ch18s04.html

You can optionally use SRID in your query.
I am a bit out of my depth now as I have never used mysql spatial.


Hope that helps
Saber


> Saber
>
> Ogrinfo -summary on the MySQL table as saved by QGIS says:
>
> Layer name:	 geom_test
> Geometry: Unknown (any)
> Feature Count: 0
> Layer SRS WKT:
> (unknown)
> FID Column = id
> Geometry Column = Geometry
>
>
> Ogrinfo -summary on the table after running SELECT AsWKB(Geometry) says:
>
> Layer name:	 geom_test_converted
> Geometry: Unknown (any)
> Feature Count: 34
> Layer SRS WKT:
> (unknown)
> FID Column = id
> Geometry Column = Geometry
>
> There is no projection in the layer, QGIS seems to assume that it is
> lat/lon
> and then reprojects it to 900913, which is what I am using overall.
>
> Maybe the lack of projection in the data is confusing OGR....but I'm not
> sure that if I apply projection to the layer via QGIS, it will get saved
> as
> part of the MySQL data??
>
>
> Chris
>
>
>
> -----Original Message-----
> From: Saber Razmjooei [mailto:razmjooeis at faunalia.co.uk]
> Sent: 27 July 2011 14:04
> To: Chris Green
> Cc: qgis-user at lists.osgeo.org
> Subject: RE: [Qgis-user] QGIS -> MySQL -> Mapserver ??
>
> What does ogrinfo -al say about your geometry? Are those numbers located
> in the right place? Just to make sure your layer projection is set
> properly.
>
>
>> Hi Saber
>>
>> I tried that, and the when the MySQL data is saved as an ESRI shapefile
>> the shapefile opens correctly.
>>
>> I have been experimenting with converting the MySQL BLOB data. For
>> example
>> if I try
>>
>> SELECT AsText(Geometry) I get a line string which looks OK and could be
>> used to create a line feature in MapServer using PHP Mapscript. Then
>> when
>> I try:
>>
>> SELECT AsWKB(Geometry) I get a new set of BLOBs which can be recognised
>> by
>> OGRinfo as features (progress!!!) but still not displayed by MapServer.
>>
>> My conclusion is that QGIS is using some internal format to store MySQL
>> linestrings which is not properly understood by OGR.
>>
>> Any idea where I could find out any more on this?
>>
>>
>> Chris
>>
>>
>>
>> -----Original Message-----
>> From: Saber [mailto:razmjooeis at faunalia.co.uk]
>> Sent: 27 July 2011 10:55
>> To: Chris Green
>> Cc: qgis-user at lists.osgeo.org
>> Subject: RE: [Qgis-user] QGIS -> MySQL -> Mapserver ??
>>
>> As a test, worth converting the mysql vector to shapefile to see if the
>> vector is shown properly.
>>
>> Also checking webserver logs report for mapsever requests of the MySQL
>> vector might help.
>>
>> Chris Green <chris.green at ibstv.co.uk> wrote:
>>
>>>Hi Saber
>>>
>>>
>>>
>>>Thanks for the suggestion. When I run ogrinfo -formats I see that MySQL
>>> is
>>>included, so that is not the problem.
>>>
>>>It seems that the issue is about the way that I created and store the
>>> line
>>>data, because I have tried with some point data and I don't have this
>>>problem - I can create, save, and display points with QGIS and then both
>>> OGR
>>>and MapServer are OK. But with line data it is different.
>>>
>>>
>>>
>>>I can only assume that QGIS is sending the line data to MySQL in a
>>> format
>>>that QGIS recognises when the data is retrieved, but which is not
>>> recognised
>>>by OGR / MapServer.
>>>
>>>
>>>
>>>Going to PostGIS would have to be a last resort, I have a lot of effort
>>>invested in MySQL ..
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>Chris
>>>
>>>
>>>
>>>From: Saber Razmjooei [mailto:razmjooeis at faunalia.co.uk]
>>>Sent: 27 July 2011 09:35
>>>To: Chris Green
>>>Cc: qgis-user at lists.osgeo.org
>>>Subject: Re: [Qgis-user] QGIS -> MySQL -> Mapserver ??
>>>
>>>
>>>
>>>Chris
>>>
>>>I presume your ogr can handle mysql if you can open it in QGIS. But to
>>>confirm could you run:
>>>
>>>ogrinfo --formats
>>>
>>>
>>>
>>>
>>>from terminal and check if you have
>>>
>>>-> "MSSQLSpatial" (read/write)
>>>
>>>Is your database large? Have you considered using PostGIS?
>>>
>>>Cheers
>>>Saber
>>>
>>>
>>>On 27/07/11 07:45, Chris Green wrote:
>>>
>>>
>>>
>>>Hi QGIS users
>>>
>>>
>>>
>>>I have a problem getting Mapserver to display vector line features which
>>> I
>>>created with QGIS and saved as spatial objects in MySQL...I asked for
>>> some
>>>advice on this from the Mapserver forum yesterday but had no responses,
>>>perhaps someone in this forum has some experience to offer?
>>>
>>>
>>>
>>>As explained in the question below, I can create line features in QGIS,
>>> save
>>>them in a MySQL table, and recall and display them with QGIS without any
>>>problem.  But the same features do not appear to be recognised by
>>> OGRInfo
>>> or
>>>by Mapserver. The line features are saved as BLOB data in a MySQL
>>> MyISAM
>>>table in a column which is defined as type 'geometry'. MySQL can create
>>> a
>>>spatial index of these features.
>>>
>>>
>>>
>>>The table is accessed by QGIS as a database layer via the 'Add Vector
>>> Layer'
>>>dialogue. I am using QGIS 1.7.
>>>
>>>
>>>
>>>It's not really a QGIS problem, but I spent all day wrestling with this
>>>yesterday but have not solved it.  Any suggestions?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>Chris
>>>
>>>
>>>
>>>##########################################################################
> ##
>>>###
>>>
>>>
>>>
>>>
>>>
>>>I'm having some trouble with an OGR connection to a MySQL database. The
>>> data
>>>I'm trying to access was created using QGIS as a line layer and saved in
>>> a
>>>MySQL table as a BLOB in a geometry column. I can access and properly
>>>display this line data using QGIS, but not so far via Mapserver -
>>> nothing
>>> is
>>>drawn and I can't see any error.
>>>
>>>
>>>
>>>I am using Mapserver 6.0 and my mapfile is very similar to the example
>>> in
>>>theMapserver / MYSQL  documentation:
>>>
>>>
>>>
>>>LAYER
>>>
>>>       NAME "testLine"
>>>
>>>       TYPE   LINE
>>>
>>>       STATUS  DEFAULT
>>>
>>>       CONNECTIONTYPE  OGR
>>>
>>>       CONNECTION  "mysql:testDB,user=admin,password=password,port=3306"
>>>
>>>       DATA "SELECT Geometry from testLine"
>>>
>>>       CLASS
>>>
>>>              STYLE
>>>
>>>                     COLOR  0 0 0
>>>
>>>                     WIDTH  2
>>>
>>>              END # style
>>>
>>>       END # class
>>>
>>>END # layer
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>If I look at the MySQL table using OGRinfo I see the following:
>>>
>>>
>>>
>>>Layer name: testLine
>>>
>>>Geometry: Unknown (any)
>>>
>>>Feature Count: 0
>>>
>>>Layer SRS WKT:
>>>
>>>(unknown)
>>>
>>>FID Coumn = id
>>>
>>>Geometry Column = Geometry
>>>
>>>
>>>
>>>
>>>
>>>The issue seems to be that OGRInfo and therefore MapServer does not
>>>recognise any features in the table, but QGIS using access to the same
>>> table
>>>finds and displays the features.
>>>
>>>
>>>
>>>
>>>
>>>Anyone have any ideas what I am doing wrong?
>>>
>>>
>>>
>>>
>>>
>>>Chris
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>_______________________________________________
>>>Qgis-user mailing list
>>>Qgis-user at lists.osgeo.org
>>>http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>>
>>>
>>
>>
>>
>
>
>
>
>

More information about the Qgis-user mailing list