Geek Answers Handbook

Convert WKT polys to MySQL in Google Maps polys

I have a column of WKT POLYGON values ​​in MySQL (I inherited db). Policies are requested and displayed on Google Maps. Since overlaying Google Maps polygons requires an array of points, the previous user converted the WKT values ​​to the coordinates of the pairs and saved them in another column. It really works pretty well, but not good enough.

Firstly, the conversion was sometimes erroneous, and for two I am looking for ways to do this faster.

Re. The first question, I have to re-implement this, and I'm looking for a converter that converts WKT poly to a coordinate string. I think I could use this to either write a stored procedure that will request a WKT column and spit out a JSON text string that can be easily converted to Google Maps policies, or even pre-process all WKT policies and save them as text. already done, but this time with the correct values.

So, I'm really looking for a function to convert WKT to the coordinate string of its component coordinates, sort of like

SELECT AsStringOfCoords(WKT_Column) FROM table WHERE condition

where AsStringOfCoords() will be my custom function.

+7
source share
1 answer

I wrote a small C ++ program for creating WKT MySQL polygons for KML polygons. This thing works as follows:

  • Read database information
  • create kml document
  • Modify the information and print it in a file.
  • You can call the new kml in googlemaps and it shows pretty nice.

The source code is here ...

 #include <iostream> #include <string> /* * Database includes... */ #include <mysql_connection.h> #include <cppconn/driver.h> #include <cppconn/exception.h> #include <cppconn/resultset.h> #include <cppconn/statement.h> #include <cppconn/prepared_statement.h> #include "../iolib/IOCoreFuncs.h" #include "../iolib/ioconfigurador.h" using namespace std; using namespace sql; using namespace IOCore; sql::Connection * conectaDB(string dbSvr, string dbUsr, string dbPwd, string dbNombre); int main(int argc, char **argv) { string qry, arproc; Connection * dbCon; Statement * stmt; IOConfigurador * miConf; ResultSet * rs; //Cargar configuración... if (argc == 3) { arproc = argv[2]; } else { cout << "Using mode: sqltokml <polygon id> <file kml to export>\n"; return 1; } dbCon = conectaDB("dbserver", "dbuser"), "dbpasswd", "dbname"); stmt = dbCon->createStatement(); qry = "SELECT name, astext(geoarea) from " + "table name" + " where id = '" + argv[1] + "';"; rs = stmt->executeQuery(qry); if (rs->rowsCount() > 0) { string polnombre, polcoords; string salida; while (rs->next()) { ofstream sale; polnombre = rs->getString(1); polcoords = rs->getString(2); salida = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" "<kml xmlns=\"http://www.opengis.net/kml/2.2\" xmlns:gx=\"http://www.google.com/kml/ext/2.2\" xmlns:kml=\"http://www.opengis.net/kml/2.2\" xmlns:atom=\"http://www.w3.org/2005/Atom\">\n" "<Document>\n" "<name>" + polnombre + ".kml</name>\n" "<Style id=\"sh_ylw-pushpin3\">\n" "<IconStyle>\n" "<scale>1.3</scale>\n" "<Icon>\n" "<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>\n" "</Icon>\n" "<hotSpot x=\"20\" y=\"2\" xunits=\"pixels\" yunits=\"pixels\"/>\n" "</IconStyle>\n" "<LineStyle>\n" "<color>467f5500</color>\n" "<width>3</width>\n" "</LineStyle>\n" "<PolyStyle>\n" "<color>46ff5555</color>\n" "</PolyStyle>\n" "</Style>\n" "<StyleMap id=\"msn_ylw-pushpin10\">\n" "<Pair>\n" "<key>normal</key>\n" "<styleUrl>#sn_ylw-pushpin30</styleUrl>\n" "</Pair>\n" "<Pair>\n" "<key>highlight</key>\n" "<styleUrl>#sh_ylw-pushpin3</styleUrl>\n" "</Pair>\n" "</StyleMap>\n" "<Style id=\"sn_ylw-pushpin30\">\n" "<IconStyle>\n" "<scale>1.1</scale>\n" "<Icon>\n" "<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>\n" "</Icon>\n" "<hotSpot x=\"20\" y=\"2\" xunits=\"pixels\" yunits=\"pixels\"/>\n" "</IconStyle>\n" "<LineStyle>\n" "<color>467f5500</color>\n" "<width>3</width>\n" "</LineStyle>\n" "<PolyStyle>\n" "<color>46ff5555</color>\n" "</PolyStyle>\n" "</Style>\n" "<Folder>\n" "<name>" + polnombre + "</name>\n" "<Placemark>\n" "<name>" + polnombre + "</name>\n" "<styleUrl>#msn_ylw-pushpin10</styleUrl>\n" "<Polygon>\n" "<tessellate>1</tessellate>\n" "<outerBoundaryIs>\n" "<LinearRing>\n" "<coordinates>\n"; //Coordinates tranformation... polcoords = polcoords.substr(9, polcoords.size() - 11); vector< string > lascoords = split(polcoords, ","); for (unsigned i = 0; i < lascoords.size(); i++) { salida += lascoords[i].substr(0, lascoords[i].find(" ")) + ","; salida += lascoords[i].substr(lascoords[i].find(" ") + 1) + ",0 "; } salida += "\n</coordinates>\n" "</LinearRing>\n" "</outerBoundaryIs>\n" "</Polygon>\n" "</Placemark>\n" "</Folder>\n" "</Document>\n" "</kml>"; sale.open(arproc.c_str(), ios::out | ios::app); sale << salida ; sale.close(); } } rs->close(); stmt->close(); dbCon->close(); } sql::Connection * conectaDB(string dbSvr, string dbUsr, string dbPwd, string dbNombre) { sql::Connection * retval; sql::Driver *ctrl; try { ctrl = get_driver_instance(); retval = ctrl->connect(dbSvr, dbUsr, dbPwd); retval->setSchema(dbNombre); } catch (sql::SQLException &err) { cout<<"Errors... :( "<<err.what()<<"\ngoing out\n"; retval = 0; } return retval; }

I hope this can help you. It's easy to translate this to MySQL stored proc or use it inside PHP or other languages ​​... I also have some php / javascript scripts to do the same with dots.

0
source

More articles:


All Articles