I am working on a real estate website, and each property has a latitude and longitude.
We also have a database of schools (among other things), and each school has a list of geographic points that relate to the schools geographic boundaries. We store this information currently in a large string formatted to the google maps array standard (so it can be read and injected directly in to a google maps object in HTML).
With a given property (lat/lng), how can we easily find which schools are available to that property?
What I am looking for here is the best way to design our database going forward to make this common lookup easy and fast. To be honest even given all the points as individual objects in C# I have no idea how to determine if a point falls within its area.
Best Answer
Spatial databases have spatial data types, so when you use e.g. a PostGIS ( http://postgis.net/ ) /MS SQL Server ( https://msdn.microsoft.com/library/bb933790.aspx ) /... database for storage you can offload the spatial queries to the database and say (pseudocode):
assuming your schools and properties have their geometries stored in a geometry / spatial column called GEOM.
The real advantage is that you can easily build spatial indexes that will speed upthose queries, even if you have lots of spatially distributed geometries.