[gdal-dev] drawing line using OGR and mysql in mapserver
Chaitanya kumar CH
chaitanya.ch at gmail.com
Fri Mar 2 07:35:12 EST 2012
Murat,
Put aside linestrings and geometries. First you need to have a wkt
representation of your line segment. Your MySQL query is returning a
number. That should be an indicator that your query was wrong. You were
using the wrong operator. You need to convert the latitude and longitude
values to string and concatenate the them with the other strings to make up
the wkt of linestring geometries. Otherwise, MySQL is just adding the
string and decimal values mathematically.
Use the CONVERT function as described in the website:
http://www.geeksengine.com/database/single-row-functions/conversion-functions.php
On Fri, Mar 2, 2012 at 5:38 PM, Murat Beyhan <beyhan at deprem.gov.tr> wrote:
> Chaitanya,
>
> I could not do this using sql sorry.
>
> But I have an another idea is it good way to store these data as a
> spatial database in MySql.
> I try this but when I insert data to mysql table
> I have problem .
> I have created a table
> as follows
>
> eq_id varchar(14)
> fault linestring
>
> then I will insert data (if I success how to do that)
> then I will use this table from mapserver.
> is this proper way.
> Or how to declare geom column as string at sql statement as follows
>
> create view lineview as select eq_id, "LINESTRING(" + lon1 + " " + lat1
> + "," + lon2 + " " + lat2 + ")" as geom from sfault
>
>
> please give your idea...
>
>
>
> On Thu, 2012-03-01 at 14:40 +0530, Chaitanya kumar CH wrote:
> > Murat,
> >
> >
> > It's not working. the geom field should not be 'Real'. It should be
> > 'String'. Try something else other than the '+' operator in the SQL
> > syntax to create the geom field in the view.
> >
> > On Thu, Mar 1, 2012 at 12:48 PM, Murat Beyhan <beyhan at deprem.gov.tr>
> > wrote:
> > Chaitanya,
> >
> > I have had a progress about VRT data access
> > but I afraid something wrong in mapserver .map file.
> >
> > here is the test.vrt file and I use in map file with
> > connection ogr
> > functionality. :
> >
> > <OGRVRTDataSource>
> > <OGRVRTLayer name="dynamic">
> >
> <SrcDataSource>MYSQL:dynamic,user=root,password=mysql_passwd,host=localhost,port=3306,tables=lineview</SrcDataSource>
> > <SrcSql>select eq_id, geom from lineview</SrcSql>
> > <GeometryType>wkbLineString</GeometryType>
> > <GeometryField encoding="WKT" field="geom"/>
> >
> > </OGRVRTLayer>
> > </OGRVRTDataSource>
> >
> > then the command
> >
> >
> > [murat at localhost stations]$ ogrinfo -ro -al test.vrt
> > INFO: Open of `test.vrt'
> > using driver `VRT' successful.
> >
> >
> > Layer name: dynamic
> > Geometry: Line String
> > Feature Count: 2
> > Layer SRS WKT:
> > (unknown)
> >
> > eq_id: String (42.0)
> > geom: Real (23.0)
> > OGRFeature(dynamic):0
> > eq_id (String) = 20111023101120
> > geom (Real) = 164
> >
> > OGRFeature(dynamic):1
> > eq_id (String) = 20100308023229
> > geom (Real) = 163
> >
> >
> >
> > it works now but mapserver gives an error as follows:
> >
> > Server error!
> > The server encountered an internal error and was unable to
> > complete your
> > request.
> >
> > Error message:
> > Premature end of script headers: mapserv
> >
> > If you think this is a server error, please contact the
> > webmaster.
> >
> >
> > Error 500
> > 10.14.0.67
> > Apache/2.2.14 (Mandriva Linux/PREFORK-1.6mdv2010.0)
> >
> >
> >
> > I think I will solve it with your help.
> >
> > Many thanks
> >
> > Murat
> >
> >
> > On Thu, 2012-03-01 at 09:22 +0530, Chaitanya kumar CH wrote:
> > > Murat,
> > >
> > > You should check MySQL's SQL syntax to concatenate strings
> > and
> > > decimals to produce a string.
> > >
> > > On Wednesday, February 29, 2012, Murat Beyhan
> > <beyhan at deprem.gov.tr>
> > > wrote:
> > > > Chaitanya,
> > > >
> > > > here is the structure of table on database
> > > >
> > > > sfault :
> > > >
> > > > eq_id varchar(14)
> > > > lat1 decimal(7,4)
> > > > lon1 decimal(7,4)
> > > > lat2 decimal(7,4)
> > > > lon2 decimal(7,4)
> > > >
> > > > lineview :
> > > >
> > > >
> > > > eq_id varchar(14)
> > > > geom double
> > > >
> > > >
> > > > it seams geom as double
> > > > I thing it should be geometry isn't it?
> > > >
> > > > may be I have to change structure of lineview by changing
> > following
> > > > Roberts' suggestion to create view on database
> > > >
> > > > create view lineview as select eq_id, "LINESTRING(" +
> > lon1 + " " +
> > > lat1
> > > > + "," + lon2 + " " + lat2 + ")" as geom from sfault
> > > >
> > > >
> > > > but I'm not sure for the line.
> > > >
> > > > VTR for points, I have tested and works well
> > > >
> > > >
> > > > but for line still could not achieved yet, please give me
> > another
> > > idea.
> > > >
> > > > here is test.vrt file
> > > >
> > > > <OGRVRTDataSource>
> > > > <OGRVRTLayer name="sta">
> > > >
> > >
> >
> <SrcDataSource>MYSQL:stations,user=root,password=mysql_passwd,host=localhost,port=3306,tables=sta</SrcDataSource>
> > > > <SrcSQL>SELECT sta_id as name,type,latitude,longitude from
> > sta where
> > > > type like "%SMACH%"</SrcSQL>
> > > > <GeometryType>wkbPoint</GeometryType>
> > > > <GeometryField encoding="PointFromColumns" x="longitude"
> > > y="latitude"/>
> > > > </OGRVRTLayer>
> > > > </OGRVRTDataSource>
> > > >
> > > > and following code gives successful result as you see
> > > >
> > > >
> > > > ogrinfo -ro -al test.vrt
> > > >
> > > > INFO: Open of `test.vrt' using driver `VRT' successful.
> > > >
> > > > Layer name: sta
> > > > Geometry: Point
> > > > Feature Count: 12
> > > > Layer SRS WKT:
> > > > (unknown)
> > > > name: String (12.0)
> > > > type: String (24.0)
> > > > latitude: Real (5.4)
> > > > longitude: Real (5.4)
> > > > OGRFeature(sta):0
> > > > name (String) = 0617
> > > > type (String) = SMACH
> > > > latitude (Real) = 40.4569
> > > > longitude (Real) = 32.6319
> > > > POINT (32.631900000000002 40.456899999999997)
> > > >
> > > > OGRFeature(sta):1
> > > > name (String) = 0618
> > > > type (String) = SMACH
> > > > latitude (Real) = 40.4798
> > > > longitude (Real) = 32.4555
> > > > POINT (32.4555 40.479799999999997)
> > > >
> > > > .
> > > > .
> > > > .
> > > > .
> > > >
> > > >
> > > > As you see this works well.
> > > >
> > > > I hope you can catch point what is wrong or lack of on my
> > works....
> > > >
> > > >
> > > > Thanks again
> > > >
> > > >
> > > >
> > > >
> > > > On Tue, 2012-02-28 at 18:08 +0530, Chaitanya kumar CH
> > wrote:
> > > >> Murat,
> > > >>
> > > >>
> > > >> I see that your view, lineview, doesn't give the
> > expected geometry
> > > >> type with ogrinfo. It is showing the datatype as Real.
> > Check if the
> > > >> datatype of geom is actually string/text.
> > > >>
> > > >> On Tue, Feb 28, 2012 at 12:49 PM, Murat Beyhan
> > > <beyhan at deprem.gov.tr>
> > > >> wrote:
> > > >> Chaitanya,
> > > >>
> > > >>
> > > >> Map file which is try to draw line as follows
> > > >> are there any problem on the code
> > > >>
> > > >> LAYER
> > > >> NAME sfault1
> > > >> CONNECTIONTYPE OGR
> > > >> CONNECTION '<OGRVRTDataSource>
> > > >> <OGRVRTLayer name="lineview">
> > > >>
> > > >>
> > >
> >
> <SrcDataSource>MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306,tables=lineview</SrcDataSource>
> > > >> <SrcLayer>lineview</SrcLayer>
> > > >> <GeometryType>wkbLineString</GeometryType>
> > > >> <GeometryField encoding="WKT" field="geom"/>
> > > >> <LayerSRS>WGS84</LayerSRS>
> > > >> <FID>eq_id</FID>
> > > >> </OGRVRTLayer>
> > > >> </OGRVRTDataSource>'
> > > >> DATA sfault1
> > > >> STATUS on
> > > >> TYPE line
> > > >> CLASS
> > > >> NAME "Source Fault"
> > > >> SYMBOL "cline"
> > > >> SIZE 3
> > > >> COLOR 0 0 255
> > > >> END
> > > >> END
> > > >>
> > > >>
> > > >> ogrinfo
> > > >>
> > >
> >
> MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306
> > > >> sfault -summary
> > > >> INFO: Open of
> > > >>
> > >
> >
> `MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306'
> > > >> using driver `MySQL' successful.
> > > >>
> > > >> Layer name: sfault
> > > >> Geometry: None
> > > >> Feature Count: 1
> > > >> Layer SRS WKT:
> > > >> (unknown)
> > > >> eq_id: String (14.0)
> > > >> lat1: Real (7.4)
> > > >> lon1: Real (7.4)
> > > >> lat2: Real (7.4)
> > > >> lon2: Real (7.4)
> > > >>
> > > >> **************linewiew created by following sql
> > > >> statement:*************
> > > >>
> > > >>
> > > >> create view lineview as select eq_id,
> > "LINESTRING(" + lon1
> > > +
> > > >> " " + lat1
> > > >> + "," + lon2 + " " + lat2 + ")" as geom from
> > sfault
> > > >>
> > > >>
> > > >> then
> > > >>
> > > >> [murat at localhost ~]$ ogrinfo
> > > >>
> > >
> >
> MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306
> > > >> lineview -summary
> > > >> INFO: Open of
> > > >>
> > >
> >
> `MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306'
> > > >> using driver `MySQL' successful.
> > > >>
> > > >> Layer name: lineview
> > > >> Geometry: None
> > > >> Feature Count: 1
> > > >> Layer SRS WKT:
> > > >> (unknown)
> > > >> eq_id: String (14.0)
> > > >> geom: Real (0.0)
> > > >>
> > > >>
> > > >>
> > >
> > > --
> > > Best regards,
> > > Chaitanya kumar CH.
> > >
> > > +91-9494447584
> > > 17.2416N 80.1426E
> > >
> >
> > > --
> > > This message has been scanned for viruses and
> > > dangerous content by MailScanner, and is
> > > believed to be clean.
> >
> >
> >
> > Murat BEYHAN
> >
> > Jeofizik Y.Müh.
> > T.C. Başbakanlık
> > Afet ve Acil Durum Yönetimi Başkanlığı
> > Deprem Dairesi Başkanlığı
> > Eskişehir Yolu 12. Km.
> > Lodumlu/ANKARA
> > Tel: 312 2872680-1556
> > email:beyhan at deprem.gov.tr
> >
> >
> >
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by MailScanner, and is
> > believed to be clean.
> >
> >
> >
> >
> >
> >
> > --
> > Best regards,
> > Chaitanya kumar CH.
> >
> > +91-9494447584
> > 17.2416N 80.1426E
> >
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by MailScanner, and is
> > believed to be clean.
>
>
>
> Murat BEYHAN
>
> Jeofizik Y.Müh.
> T.C. Başbakanlık
> Afet ve Acil Durum Yönetimi Başkanlığı
> Deprem Dairesi Başkanlığı
> Eskişehir Yolu 12. Km.
> Lodumlu/ANKARA
> Tel: 312 2872680-1556
> email:beyhan at deprem.gov.tr
>
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
--
Best regards,
Chaitanya kumar CH.
+91-9494447584
17.2416N 80.1426E
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20120302/3b5f8736/attachment-0001.html
More information about the gdal-dev
mailing list