R: Re: [gdal-dev] create kml file using MySQL database or
shape file
Murat Beyhan
beyhan at deprem.gov.tr
Wed May 4 09:14:03 EDT 2011
Dear Sigfrido
Thanks for your advice
yes when I add g to sql statement the script created kml file with layer
I achieved. But the result same the point doesn't seem on the map
any way I hope I will succeed. Thanks for your help
KML file as follows by using geometry column
<?xml version="1.0" encoding="utf-8" ?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><Folder><name>sql_statement</name>
<Schema name="sql_statement" id="sql_statement">
<SimpleField name="Name" type="string"></SimpleField>
<SimpleField name="Description" type="string"></SimpleField>
<SimpleField name="koy" type="string"></SimpleField>
<SimpleField name="lat" type="float"></SimpleField>
<SimpleField name="lng" type="float"></SimpleField>
<SimpleField name="sisint" type="int"></SimpleField>
</Schema>
<Placemark>
<ExtendedData><SchemaData schemaUrl="#sql_statement">
<SimpleData name="koy">SEYHAN</SimpleData>
<SimpleData name="lat">36.9871</SimpleData>
<SimpleData name="lng">35.3227</SimpleData>
<SimpleData name="sisint">1</SimpleData>
</SchemaData></ExtendedData>
<Point><coordinates>36.987099999999998,35.322699999999998</coordinates></Point>
</Placemark>
without using g KML files like this look the <Point><coordinates> line.
<?xml version="1.0" encoding="utf-8" ?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><Folder><name>sql_statement</name>
<Schema name="sql_statement" id="sql_statement">
<SimpleField name="Name" type="string"></SimpleField>
<SimpleField name="Description" type="string"></SimpleField>
<SimpleField name="koy" type="string"></SimpleField>
<SimpleField name="lat" type="float"></SimpleField>
<SimpleField name="lng" type="float"></SimpleField>
<SimpleField name="sisint" type="int"></SimpleField>
</Schema>
<Placemark>
<ExtendedData><SchemaData schemaUrl="#sql_statement">
<SimpleData name="koy">SEYHAN</SimpleData>
<SimpleData name="lat">36.9871</SimpleData>
<SimpleData name="lng">35.3227</SimpleData>
<SimpleData name="sisint">1</SimpleData>
</SchemaData></ExtendedData>
</Placemark>
there is no <Point><coordinates> line
But google- earth doesn't shows the points on the map...
On Wed, 2011-05-04 at 15:03 +0200, sigfrido at tiscali.it wrote:
> Hi Murat,
>
> try to explicitly add g (the geometric column) in the select clause:
>
> SELECT g, koy,lat,lng,sisint from deneme2
>
> otherwise ogr2ogr won't be able to see it when creating KML layer.
>
> All the best
>
> Sig
>
> ----Messaggio originale----
> Da: beyhan at deprem.gov.tr
> Data: 04/05/2011 14.25
> A: "sigfrido at tiscali.it"<sigfrido at tiscali.it>
> Cc: "gdal-dev at lists.osgeo.org"<gdal-dev at lists.osgeo.org>
> Ogg: Re: R: Re: [gdal-dev] create kml file using MySQL database or
> shape file
>
>
> Dear Sigfrido
>
> As you told me I have added a geometry column to my database as g
>
> and
>
> I executed following sql statement
> This is the first time for me to make spatial database table . So I
> think first of all I have to learn more about Spatial database concept
> in MySQL.
>
> any way
>
> UPDATE deneme2
> SET g = GeomFromText(
> CONCAT('POINT(', CAST(lat AS CHARACTER(20)),
> ' ', CAST(lng AS CHARACTER(20)),')')
> ) ;
>
>
> than I used
>
> ogr2ogr -f KML sisint.kml
> MySQL:'afetler,host=localhost,user=root,password=mysql_123,port=3306,
> tables=deneme2' -sql "SELECT koy,lat,lng,sisint from deneme2"
>
>
>
> ogrinfo -ro sisint.kml
>
> The problem is there is no layer in KML file I think.
>
>
>
> ERROR 4: No layers in KML file: sisint.kml.
> FAILURE:
> Unable to open datasource `sisint.kml' with the following drivers.
> -> ESRI Shapefile
> -> MapInfo File
> -> UK .NTF
> -> SDTS
> -> TIGER
> -> S57
> -> DGN
> -> VRT
> -> REC
> -> Memory
> -> BNA
> -> CSV
> -> GML
> -> GPX
> -> KML
> -> GeoJSON
> -> Interlis 1
> -> Interlis 2
> -> GMT
> -> SQLite
> -> ODBC
> -> PGeo
> -> OGDI
> -> MySQL
> -> PCIDSK
> -> XPlane
> -> AVCBin
> -> AVCE00
> -> DXF
> -> Geoconcept
> -> GeoRSS
> -> GPSTrackMaker
> -> VFK
>
>
>
> so I couldn't understand what is wrong or what I have to do first.
>
>
> Really I need to create such a point or polygon data extracted from
> MySQL database to KML format. Please help me
>
> Murat
>
>
> On Wed, 2011-05-04 at 12:08 +0200, sigfrido at tiscali.it wrote:
> > Hi Murat,
> >
> > I'm not so much into KML, but I think that maybe the
> > problem is with your SQL: you are not including any geometry column
> in
> > the select instruction. If you open the KML file with a text
> editor,
> > you can see if the points are being exported and with which
> > coordinates.
> >
> > Does the MySQL table have a geometric column? Or you just
> > have the lon/lat coordinates?
> > If you don't have geometry you have first
> > to add it to the MySQL table, update it with the point geometry
> created
> > from lon/lat, and then export the points in KML.
> >
> > Hope this helps
> >
> > Sig
> >
> >
> > ----Messaggio originale----
> > Da: beyhan at deprem.gov.tr
> > Data: 04/05/2011
> > 9.17
> > A: "Luca Sigfrido Percich"<sigfrido at tiscali.it>
> > Cc: "gdal-
> > dev at lists.osgeo.org"<gdal-dev at lists.osgeo.org>
> > Ogg: Re: [gdal-dev]
> > create kml file using MySQL database or shape file
> >
> > Dear Sigfrido,
> >
> >
> > Thank you for your help,
> > I corrected the script and it works but when I
> > open it with google-earth
> > points doesn't seem on the map I sent
> > snapshot of the google-earth.I
> > think problem is latitude and longitude
> > could not read by the
> > google-earth.
> > So How can I fix it...
> >
> >
> >
> > Regards.
> >
> >
> > ogr2ogr -f KML sisint.kml
> > MySQL:'afetler,host=localhost,user=root,
> > password=mysql_123,port=3306,tables=sisint' -sql "SELECT koy,
> > longitude, latitude , sisint from sisint"
> >
> > koy means village name.
> >
> > sisint menans seismic intensity value of the village
> >
> > Murat
> >
> >
> > On Tue,
> > 2011-05-03 at 17:33 +0200, Luca Sigfrido Percich wrote:
> > > Hi Murat,
> > >
> >
> > > Il giorno mar, 03/05/2011 alle 16.57 +0300, Murat Beyhan ha
> scritto:
> >
> > > > ogr2ogr -f KML simgmt.kml simgmt.shp -dsco NameField=RegionName
> > > >
> > this can create a file but strangely data collected at one point.
> > >
> > >
> > Probably it is a Spatial Reference System related problem: you
> should
> > >
> > specify the source shapefile SRS. Have a look at the KML file and
> see
> > if
> > > the points have different coordinates, and see what SRS has been
> > >
> > declared for it. Have a look at the -a_srs, -t_srs and -s_srs
> options
> > of
> > > ogr2ogr.
> > >
> > >
> > > > ogr2ogr -f KML sisint.kml MySQL
> > :'host=localhost user=root
> > > > password=mysql_pswd port=3306
> > dbname=afetler tables=sisint' -sql "SELECT
> > > > latitude,longitude,
> > sisint from sisint"
> > >
> > > Try with no space between MySQL and :, and
> > specifying the database name
> > > before any other option:
> > >
> > > ogr2ogr -f
> > KML sisint.kml
> > > MySQL:'afetler,host=localhost,user=root,
> > password=mysql_pswd,port=3306,tables=sisint' -sql "SELECT latitude,
> > longitude,sisint from sisint"
> > >
> > > All the best
> > >
> > > Sig
> > >
> >
> >
> >
> > --
> >
> > Murat BEYHAN
> > Geophysical Engineer, M.Sc.
> > Prime Ministry
> > Disaster and
> > Emergency Management Presidency
> > Earthquake Department
> > Address:
> > Eskisehir 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.
> >
> >
> >
> >
> >
> >
> > Non temiamo alcun confronto: Tiscali ha l'Adsl più veloce d'Italia!
> >
> > Risparmia con Tutto Incluso Light: Voce + Adsl 20 mega a soli 17,95
> € al mese per 12 mesi.
> >
> > http://abbonati.tiscali.it/telefono-
> adsl/prodotti/tc/tuttoincluso_light/?WT.mc_id=01fw
> >
>
>
>
> 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.
>
>
>
>
>
> SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
> http://wiphone.tiscali.it
>
>
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.
More information about the gdal-dev
mailing list