What database and java library to use for Spatial Analysis?

Question

I have been working on a web project that stores locations for users. The project uses currently MySql 5.5 and JPA 2 for mapping the relation database, together with EJB 3.1 as the middle tier. I store longitude and latitude data in Decimal data types in MySql.

I want to expand the project so the user can search for Points (GPS coordinates) near the location he/she marks out (through using Google Map API 3) on the map.

I need some hints and suggestions before starting transforming or changing components in my project. If possible with tutorial and how to achieve the changes and what tools (library, dev tools, etc.) to use. Here are my questions.

1. Can I use the Spatial extension in MySql (using data types like Point) and map this to entities that are supported by JPA 2 (like DataNucleus library, if they are supported by JPA 2). That are light weight and can persist them self through the persistent layer.
2. Is it better to move to PostGIS and Postgres (which seem to have better spatial support) with all the pain and effort needed to change database and using hibernate instead. Netbeans has Hibernate support but when trying with the JDBC for PostGis I stumble upon problems. Need a good tutorial to follow to get started if I go down this road.
3. Using the infrastructure that is already in-place and compute the distance from lactation A and B. Similar to the way done in this article by Jan Philip Matuschek

Excuse me for clustering the questions. Regards Chris

Answer 1

MySQL's Spacial extension is usually OK if you only have point locations, but it starts to get overwhelmed if you want to find points in polygons (i.e., query by region), or use fancy indices and search algorithms. There is a really good cross comparison to help show the differences between the different spatial DBs.

I'd see that there are many more benefits to migrating to PostgreSQL and PostGIS. There are also many more GIS applications that work naively with PostGIS, such as QGIS, Geoserver, etc. The most recent version of PostGIS has a KNN nearest neighbor search operator to quickly find, for example, the nearest 25 points out of several million rows to a particular point on your map (see also here).

See the manual for JDBC and PostGIS. As for JPA, see PostGIS and JPA 2.0

As for distance calculations, see ST_Distance and ST_Distance_Spheroid.