Designing a normalize database for getting 5 nearest neighbor using MySQL spatial index

Question

I will be using MySQL with spatial index for a database, then i will query for 5 nearest neighbors from a given point(lat/long), these points will be interest points in Dominican Republic. Once that i get the 5 nearest points i will need the Name of the Location, Type of the Location, Address of the location and (lat,long) for each point.

What should be the right way to design my database? i was thinking of doing the following: Table: Name Columns: ID, Name

Table: Type Columns: ID, Type

Table: Street Columns: ID, Street

Table: Street Number Columns: ID, Street Number

Table: Borough Columns: ID, Borough

Table: City Columns: ID, City

Table: Geopoint Columns: Latitude, Longitude, GeometryPoint

Am i missing anything? Any Suggestion. Does anyone knows some place where i can read about it? This is a school project, should be using this along with mobile app to show the users 5 nearest point of interest(gas station, restaurants, Pharmacy, etc) depending of their type of choice to their actual location.

Also where i can fetch the data? I mean is there a way where i can get all the streets name, boroughs, city of my country and put it in my database. The points of interest i can create myself i think.

Answer 1

first of all the structure looks good in terms of normalization. You can read some more about this topic by just googling for "database normalization rules". But you should be aware of the fact that you will get complex queries by separating each property (Street-table, street-number-table etc.). To get a result like "POI XY, Streetname 2, Somecity, lat=42.4, long=13.2" you would have to join at least four tables. If performance is not an issue, go ahead ;-)

You can fetch data (if available) from openstreemap.org or some poi's from geonames.org

Regards, Martin

Answer 2

I know this is a homework assignment but I would reccomend against using MySQL spatial - it has not implemented most of the spatial functions and those it does implement are just boundbox implementations. I would reccomend either PostGIS, spatialLite or MongoDB spatial.