Hi all; This is a 4 part question that I think crosses several parts. Here goes. I need to do the following: Feed in an entered address and get back the correct address (or a "can't find it". From that address get the location of that address. Save that location using EntityFrameworks into the underlying SQL Database. Search the database for all users within N miles of a specified location. I've found a ton of stuff on this searching the web - none of which works. I can't even find a class to store the location that I can find a matching library for. So help please. thanks - dave

to standardize and verify address, you will need to subscribe to a service or buy a database (with updates) to do this. be sure to pick a vendor that supplies the geo location of the address use spatial datatype. see: https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography?view=sql-server-ver16 https://learn.microsoft.com/en-us/ef/core/modeling/spatial use the STDistance to calc distance. see above docs

Get a location, save it to a DB, search against it

David Thielen 2,796

Hi all;

This is a 4 part question that I think crosses several parts. Here goes. I need to do the following:

Feed in an entered address and get back the correct address (or a "can't find it".
From that address get the location of that address.
Save that location using EntityFrameworks into the underlying SQL Database.
Search the database for all users within N miles of a specified location.

I've found a ton of stuff on this searching the web - none of which works. I can't even find a class to store the location that I can find a matching library for. So help please.

thanks - dave

Jack J Jun 24,496 Reputation points Microsoft Vendor

2023-03-06T06:27:44.77+00:00

@David Thielen, Welcome to Microsoft Q&A, to better solve your question, I need to confirm some questions with you.

Feed in an entered address and get back the correct address (or a "can't find it". From that address get the location of that address.

Do you give an example about it？ Do you mean that you want to get the specific location from a blurred location?

Save that location using EntityFrameworks into the underlying SQL Database. Search the database for all users within N miles of a specified location.

If you want to use ef to save or search data, do you have a designed entity(class) for it?
David Thielen 2,796 Reputation points

2023-03-06T17:57:37.7866667+00:00
If I put in One Microsoft Way, Redmond, WA I want to get back 1 Microsoft Way, Redmond, WA (the canonical address).

From that address I want to get the latitude & longitude.

All I have is specific addresses. I need an Azure service to convert that to the canonical form, lat/long, and then do distance queries.

thanks - dave
Bruce (SqlWork.com) 61,731 Reputation points

2023-03-07T20:40:07.1+00:00

you use azure maps:

https://learn.microsoft.com/en-us/rest/api/maps/search/get-search-address?tabs=HTTP

1 answer

Bruce (SqlWork.com) 61,731 Reputation points

2023-03-06T16:59:23.99+00:00
to standardize and verify address, you will need to subscribe to a service or buy a database (with updates) to do this.

be sure to pick a vendor that supplies the geo location of the address

use spatial datatype. see:

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography?view=sql-server-ver16

https://learn.microsoft.com/en-us/ef/core/modeling/spatial

use the STDistance to calc distance. see above docs
Please sign in to rate this answer.
David Thielen 2,796 Reputation points

2023-03-06T18:03:15.1666667+00:00

Hi Bruce;

Yes, yes, yes - this is helping. But I need some more details:

Is there an Azure service that converts to the cannonical address & lat/long?

The links you provided I found before and from them I was not able to get anything working. The referenced classes could not be found. So, can you provide a link that gives me: 1- The NuGet packages to install to get the EF spatial classes. 1- The "using" to add for the spatial classes. 1- The calls to the Azure service to get the cannonical address & lat/long 1- The class to store that as in my EF object 1- The query to make on EF to get all users located within 10 miles of a given lat/long.

thanks - dave

David Thielen 2,796 Reputation points

2023-03-09T01:24:27.84+00:00

Bruce - this is helping a lot.

So reading this, it looks like I am handling lat/long as EF Geometry.Point objects, both to store them and for my query for "within N miles."

And the query, in the select, uses geography STDistance() for items within the range.

Is that correct?

thanks - dave
Sign in to comment

Share via

Get a location, save it to a DB, search against it

1 answer