Subscribe to this thread
Home - General / All posts - geocoding from google request?
Corentin117 post(s)
#13-Sep-22 06:37

Hi,

On one hand I have a list of place name (electrical transformer) with some technical information (power, ID, ...).

On the other hand I have the location of 1200 of these electrical transformer, but without any name or ID. My customer wants me to match these 2 lists. From the electrical single line diagram I can do the match but it will be very time consuming.

Some of the transformers bear the name of the place where they are, like "US Embassy". If I google "US Embassy of [my customer city]" I will get the position of the US embassy in this city, then if there is a transformer close to this location (bingo!) I can match the information of this transformer with its position.

Some are hotel names, school names, industry names...

I have around a thousand of such names, is there a way to automate this google search and getting the location result of the google search in a spreadsheet?

In google drive I know it is possible to put in column A french words, and ask in column B to get the result from google translation service from french to english. Is there such a function with location request (from google or whatever service)

Dimitri

7,090 post(s)
#13-Sep-22 06:51

I have the location of 1200 of these electrical transformer,

What do you mean by "location"? Latitude and longitude coordinates or something else?

From the electrical single line diagram

What is the "electrical single line diagram"? If you can do a match visually from that diagram, why not just do a spatial join?

Corentin117 post(s)
#13-Sep-22 07:02

Yes I do have the latitude/longitude of the transformer, but without their name nor their ID.

Here is a snapshot of the single line diagram. It is an electrical representation of the connection of the transformers to their source of power.

In this kind of representation you can expect the general orientation of the line to respect more or less the actual route of the physical lines : a vertical line in the single line diagram will mean that the physical line is going more or less north/south.

I also have the path (lat/long) of the lines in the city. same as for transformer, with no attribute (not a single name/ID/or technical info)

Dimitri

7,090 post(s)
#13-Sep-22 09:10

As I understand it, you have two tables and two drawings:

1. You have a table where records have latitude and longitude values, but no other attributes. At each such latitude/longitude there is a transformer.

2. You have a table that has a list of all transformers with a variety of fields in that table (it would help to see the schema for that table). None of the fields are latitude or longitude values. Some of the records may have a name or comment, like "school", but none of the records have fields that can be used for geocoding, such as 125 Main Strasse, Hamburg, Germany. Some of the records don't even have a comment value like "school".

In addition you have two drawings:

3. You have an abstract, non-geographic diagram that shows the connection of transformers to electrical lines. The illustration is too small to see how those transformers are labeled.

4. You have a drawing or table that shows the geographic location of electrical lines (perhaps as a series of lat / lon locations), but which does not have any information about the location of transformers on that line.

Is the above correct? If so, it seems you are stuck with a process that involves a lot of manual labor. Manifold can help reduce that labor, but it cannot eliminate it.

For example, I'd start by taking the lat / lon dots from 1 and doing a spatial join with 4 using a buffer on the lines so that now you have the line for most of the lat/lon dots, and can by inspection guess at the lines where the lat/lon dots are close to intersections where buffers of lines would overlap.

Next, you have to guess at which transformers in the list of ones with attributes are which lat/lon dot. That can't be fully automated because not all of the records with attributes have info in them that without ambiguity can join them to the lat/lon dots.

But by using the info that you have, and using sensible backgound layers like Points of Interest, schools and others from OpenStreetMap or Google or whatever, and by keeping multiple tables open so you can quickly copy/paste, you could evolve a workflow where fairly rapidly as you go down a line you'll be able to identify the relevant record in the attribute table that corresponds to a lat/lon dot. Copy/paste an ID field from the dots table and then you can do a join on that.

It's manual, but using Manifold effectively with plenty of screen real estate and a few opened table windows, it could go very fast, like a couple of records per minute. One long day and you're done.

Corentin117 post(s)
#14-Sep-22 07:52

Thank you, I reached the same conclusion...

dchall8
955 post(s)
#16-Sep-22 05:31

You might want to double check with the provider of the table with the names to determine how they designated the names. In larger scale systems it is not uncommon for common names of objects to be misleading.

oeaulong

427 post(s)
#14-Sep-22 15:27

I guess I would find some way of generating a mid-level connective component. (e.g. zipcode areas, county, townships, municipal) and try to get somewhat close to labeling the spatial location with this component and updating the addressed location with the same field. I think you will find many pairings are obvious at that point and others may take further iterations to chase down. If I saw this sort of project coming at me only once I would then press to work through the tedium, hopefully with some efficiency. If this was going to be a cyclic effort in the future, it may be worthwhile trying to automate the assignment of ID -> Location table.

Interesting problem. Good Luck.

adamw


10,175 post(s)
#13-Sep-22 09:33

You can pass names through the geocoder using SQL in 9, example help topic:

Street Address Geocoding

For Google, you will have to get an API key.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.