[Geoserver-users] Querying of shapefiles in MySQL

GeoServer GeoServer User
1

I successfully loaded the US shapefile into a MySQL table.
If I perform a query for a specific point, I actually get 2 rows returned:

mysql> select STATE_ABBR from gis.geoserver_test where Contains(
the_geom, GeomFromText('POINT(-78.97724914550781 41.126216888427734)'
) );
+------------+
| STATE_ABBR |
+------------+
| NY |
| PA |
+------------+

It appears that MySQL is using a bounding box to determine "Contains".
Is there a way to query without the use of a bounding box ( How do I
only get 1 row back ) ?

-Thanks
Steve More

2

See:

http://intevation.de/pipermail/freegis-list/2007-July/003235.html

or use PostGIS :slight_smile:

Best regards,
Bart

Stephen More schreef:

I successfully loaded the US shapefile into a MySQL table.
If I perform a query for a specific point, I actually get 2 rows returned:

mysql> select STATE_ABBR from gis.geoserver_test where Contains(
the_geom, GeomFromText('POINT(-78.97724914550781 41.126216888427734)'
) );
+------------+
| STATE_ABBR |
+------------+
| NY |
| PA |
+------------+

It appears that MySQL is using a bounding box to determine "Contains".
Is there a way to query without the use of a bounding box ( How do I
only get 1 row back ) ?

-Thanks
Steve More

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Bart van den Eijnden
OSGIS, Open Source GIS
bartvde@anonymised.com
http://www.osgis.nl

3

Hi Stephen,

I am not an expert in mysql spatial (i have actually never used it before) but a common trick that might work using a distance function as well. Adding:

AND Distance(the_geom,GeometryFromText(...)) = 0

-Justin

Stephen More wrote:

I successfully loaded the US shapefile into a MySQL table.
If I perform a query for a specific point, I actually get 2 rows returned:

mysql> select STATE_ABBR from gis.geoserver_test where Contains(
the_geom, GeomFromText('POINT(-78.97724914550781 41.126216888427734)'
) );
+------------+
| STATE_ABBR |
+------------+
| NY |
| PA |
+------------+

It appears that MySQL is using a bounding box to determine "Contains".
Is there a way to query without the use of a bounding box ( How do I
only get 1 row back ) ?

-Thanks
Steve More

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

!DSPAM:4007,469287bd135326491211187!

--
Justin Deoliveira
The Open Planning Project
http://topp.openplans.org