All geek questions in one place

Web Services or Windows Services or SQL CLR Integration?

Good day to all,

Iā€™m a little overhead and come across some tight deadlines, so hopefully someone can offer some advice.

My starting point will be a table in a SQL Server database, two of whose fields are the x, y coordinates obtained from the gps block. I will be responsible for geocoding (getting the physical address) of these locations and writing the result to the success table or failure in the error table. I am completing a class library (C #) that actually does the geocoding process, but I am very confused to best put everything together.

Ideally, I would like to send a dataset / data from SQL Server to some type of service, where all records will be geocoded, and the result / failure for each record will be written back to the corresponding table. Since the database and the dll will be on the same server, I do not understand why I should use the web service. So the rest of my research points to either a Windows service or SQL CLR integration. My main questions are:

  • Is one of these methods more suitable than the other?
  • Is this approach acceptable (in terms of best practice)?

Any advice, comments, suggestions are welcome.

On the side of the note, if this is no longer obvious, I am a relatively novice programmer. This site was an invaluable tool in my growth as a programmer, and I really appreciate people who are in no hurry to read messages and offer their expert advice.

+4
source share
2 answers

Your geocoding code is likely to connect to the geocoding service, and this precludes SQL CLR integration. It is technically possible that accessing external resources, especially web services, from within the SQL CLR is the biggest mistake you can make. Within a few days, your server will be frozen in working hunger due to robbery of the CLR flow guaranteed.

The best approach is to use the ETL strategy. Coordinates are uploaded to an intermediate table, you start your ETL process and convert all coordinates to geocodes and write the results to success and error tables. An ETL approach typically involves revision, invasion and renewal, etc. An ETL can be an SQL job launching an application, an SSIS package, there are many ways to do this, an important bit is dividing work into intermediate tables and providing suspend / resume semantics. The dll code that you have right now (which I assume is the proxy code of the geocoding web service client) is probably 0.001% of your project.

Whether the process should be exposed as a web service, it is completely orthogonal to the ETL process, and it should be managed by your client, combining requirements and technologies. In other words, if client-side preffer code loads coordinates using WS, do it this way if you take into account large arrays of data and as long as you provide split, asynchronous, and queued semantics for the results.

+3
source

This is a very old question that I just came across. For future use, ONS has a download that you can map the zip code to the latitude / longitude (midpoint) of the zip code, and it is free. There are also many other downloads. If you're interested, this is here: https://geoportal.statistics.gov.uk/datasets/national-statistics-postcode-lookup-latest-centroids/data

+1
source

Source: https://habr.com/ru/post/1302076/

More articles:


All Articles