1
  • I have large points data and also a small bounding boxes. I want all the points present within this bounding boxes.

    • With the extent of the bbox , I want to get all the points within this bbox.

    • With the help of the below code I was able to query and also write it to a .csv file.

    • The geometry column has non-geometric type variables when comparing the geometry column of the Queried_values and Original_values.

Required Output : get correct geometry values in the SQL queried data

This is my code below :

conn = MySQLdb.connect(host="", port=, user="", passwd="", db="db1", charset="")
cur = conn.cursor()
cur.execute("""SELECT * FROM db1.table1 where (latitude >=4.139916666666666 and latitude <=52.96272222222222) and (longitude >=5.000083333333333 and longitude <=51.999944444444445)""")
set1 = cur.fetchall()
with open("point.csv", 'w', newline='') as f_handle:
    writer = csv.writer(f_handle)
    header = ['latitude','longitude','geometry']
    writer.writerow(header)
    for row in set1:
        writer.writerow(row)

My Output:

Queried_values : This is the output I get after SQL querying.

Fig1

Original_values : But I would like get the geometry as in this original file.

Fig2

1

It is a matter of representing the geometry.

You can use ST_AsText() to get a textual representation of the the geometry

The query would therefore be similar to

SELECT latitude,longitude, St_AsText(geometry)  FROM db1.table1 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.