0

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.

2

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):

select * from properties as p, schools as s where p.GEOM.STWithin(s.GEOM)

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.

  • That was my first post, and now after registering I cannot edit or comment on it. Very strange! Anyway, for til_b, we do use SQL Server 2014 ... I will look in to this geometry system your talking about. It sounds like just the thing. I had no idea SQL Server could do that, and I have been using it for many many years. – Jason1974 Nov 21 '16 at 10:44
  • Please follow these instructions ASAP to merge your two accounts: gis.stackexchange.com/help/merging-accounts In the meantime I'll convert this to a comment. – PolyGeo Nov 21 '16 at 11:15

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.