Wappler to perform mass updates and geocode

Wappler General
#1

I have a MS SQL database table of addresses that I would like to forward geocode (around 1,000)
My server model is Classic ASP or .Net

@Hyperbytes kindly provided a very useful php routine to perform a mass update of Lon/Lat fields in this thread https://community.wappler.io/t/google-maps-geocoding/3206/5 :+1:

I just wondered if there was a way of using server to connect to query the address table and loop through and update using API connect with https://opencagedata.com/demo to set the lon/lat fields in the table?

If this is possible, a brief step process would be hugely appreciated.

Thanks

#2

Might be worth checking out https://www.geoplugin.com/ rather as I have used that and know that it works great with Wappler, not only that but the author, Andy, is also a Wappler user @geoplugin so everything just works seamlessly.

#3

Thanks @psweb, I did take a look as I’ve already played with the great @geoplugin to geolocate a visitor. I just couldn’t find any reference in the documentation to geocode a given postal address.

#4

There are supporting API’s from the main one which will allow the reverse geocoding from lat long backwards, if that makes any sense. Check out the Extras here possibly and see if any of them suit your particular needs https://www.geoplugin.com/webservices/extras

#5

I have the free service but pay for the annual SSL certificate so I can use this effectively on my secure website. Other than having to get that everything else just worked and obviously the Author was super helpful too.

#6

Like mimik i need address -> lat/lon which, unless i am missing something, is sadly not offered so have to use google

#7

I think I was confused i though it was lat/lon > address and not address > lat/lon, which I am unsure if geoplugin can do. Would have to ask @geoplugin about that.

#8

Thats why I suggested https://opencagedata.com as the api will forward geocode 2,500 requests/day free of charge.

#9

So I’m able to forward geocode an address using API Connect and Google Maps API with a simple form and a text box

<form is="dmx-api-form" id="apiform" action="https://maps.googleapis.com/maps/api/geocode/json" dmx-param:sensor="'false'" dmx-param:key="'MY-KEY'" post-data="json" dmx-param:address="address.value">

This nicely returns what I need.
Lng: {{apiform.data.results[0].geometry.location.lng}}
Lat: {{apiform.data.results[0].geometry.location.lat}}

The question now is whether it’s possible to perform a mass update using Wappler API and Server Connect.

#10

I have been playing about with that very problem lately with some but not full success.
Rather than an API form, i have use an API query just like yours and put it within a repeat
The methodology is:

serverconnection1 -> get-all-address-in-data-to-be-geocoded
Repeat Children -> Loop through records in connection
get lat, lon via API
serverconnection2 -> update query, set lat, lon (matched on key field from repeat region)

I get lots of 500 errors shown in console from the update action but no debug info is shown for some reason
I suspect the problem lies with timing, i.e. the app running too fast for the multiple API requests

I may go back to the method I used in the PHP method which is fetch 1 records at a time in serverconnection1 stage where the record is not flagged as geocoded, then when updating the lat/lon also set a flag to show it is geocoded

Then place a meta refresh on the page to refresh every 1 second. It will be slower but it may work, i will pass on any success i have

Update values are passed as $_GET

This is what I currently have

image

(Serverconnect1 is the update stage)

1 Like
#11

Thanks @Hyperbytes,
Please let me know how you get on. I’m restricted to having to use Windows IIS with ASP Classic or .Net as the customer is reluctant to allow PHP on the server.

Would be great to get this working within Wappler

#12

@Hyperbytes & @mimuk - geoplugin doesn’t offer address to lat/lon because we don’t hold address information to be able to do this. Google has full map data, which we’ve toyed with the idea, just that the global map data weighs in at a whopping 1TB. Maybe some day!

We could do postcode to lat/lon, but this may not be sufficiently reliable because our postcode db has just short of 1 million entries. As a global dataset, this isn’t very big. But eg US data is quite extensive, and maybe UK too.

With lat/lon to postcode, this is easy to do since the nearest postcode is returned with an accuracy value. As it’s a “nearest” value, then a result will always be returned. To the postcode-lat/lon, this is a hit or miss approach: if we don’t hold that postcode, result would be null.

Hope this explains why it’s rare to do place/address/postcode to lat/lon - you’d need a massive database for the hit-to-miss ration to be sufficiently high to offer it as an international service.

Andy

#13

Got a UK postcode dataset if it is any use to you @geoplugin. Its pretty large, 2.5 million records or there about. Contains Postcode. lat, lon, English County, District, Country

#14

I’ve been looking for an accurate UK dataset for years. Every free or low-cost option I’ve tried is of low quality. Opencagedata seems no more accurate than Google’s geocoder as it makes similar mistakes regarding accuracy. The only accurate dataset I’ve found covering the UK is from Ordnance Survey which is very expensive to license.

A dataset of 2.5 million records is a low resolution set working only at the postcode level. A single postcode can cover around 100 metres on average, or up to 500 in rural areas. That might be good enough for some applications, but I’m still looking for address-level accuracy geocoding that doesn’t cost a king’s ransom. Maybe in another 10 years! :frowning: :smiley:

#15

Yeah, that looks good @Hyperbytes - if you can message me with a download link, I’ll add it and get code up to create a postcode to lat/lon api in the coming few days.
Then I’ll add a disclaimer for other countries in the docs so that others can send their country’s DB to import if they need complete data for their country of interest :slight_smile:

#16

.SQL format ok?

#17

Yes - I’ll export it and import it into our DB schema

#18

UK postcode data incorporated to our global postcode database thanks to @Hyperbytes

https://www.geoplugin.com/webservices/extras#postal_code_to_latitudelongitude

  • forward geocode postcodes to lat/lon using:

http://www.geoplugin.net/extras/forward_postcode.gp?format=json&postcode={URL-encoded postcode)&country={ISO 3166 2-letter Country Code}

eg:
http://www.geoplugin.net/extras/forward_postcode.gp?format=json&postcode=E1%206JE&country=GB

Will Give 
{
"geoplugin_status":200,
"geoplugin_postcode":"E1 6JE",
"geoplugin_latitude":"51.526402",
"geoplugin_longitude":"-0.078275",
"geoplugin_place":"Hackney",
"geoplugin_county":"Greater London",
"geoplugin_region":"England",
"geoplugin_regionCode":"ENG",
"geoplugin_countryCode":"GB"
}

eg2:
http://www.geoplugin.net/extras/forward_postcode.gp?format=json&postcode=02115&country=US

Will Give 
{
"geoplugin_status":200,
"geoplugin_postcode":"02115",
"geoplugin_latitude":"42.342701",
"geoplugin_longitude":"-71.092201",
"geoplugin_place":"Boston",
"geoplugin_county":"Suffolk",
"geoplugin_region":"Massachusetts",
"geoplugin_regionCode":"MA",
"geoplugin_countryCode":"US"
}

Forward geocoding places is much trickier - I’ll see what’s possible.

In any case, geoplugin doesn’t have street address data to forward geocode

#19

Done: https://www.geoplugin.com/webservices/extras#place_to_latitudelongitude

usage:
http://www.geoplugin.net/extras/forward_place.gp?format=json&place={URL_Encoded_ASCII_Place_Name}&country={ISO 3166 2-letter Country Code}

eg:
http://www.geoplugin.net/extras/forward_place.gp?format=json&place=Boston&country=US

Will Give

{“geoplugin_status”:200,“0”:{“geoplugin_latitude”:“42.3584300”,“geoplugin_longitude”:"-71.0597700",“geoplugin_place”:“Boston”,“geoplugin_region”:“Massachusetts”,“geoplugin_regionCode”:“MA”,“geoplugin_countryCode”:“US”},“1”:{“geoplugin_latitude”:“30.7918600”,“geoplugin_longitude”:"-83.7898900",“geoplugin_place”:“Boston”,“geoplugin_region”:“Georgia”,“geoplugin_regionCode”:“GA”,“geoplugin_countryCode”:“US”},“2”:{“geoplugin_latitude”:“39.7411600”,“geoplugin_longitude”:"-84.8519000",“geoplugin_place”:“Boston”,“geoplugin_region”:“Indiana”,“geoplugin_regionCode”:“IN”,“geoplugin_countryCode”:“US”},“3”:{“geoplugin_latitude”:“37.7875600”,“geoplugin_longitude”:"-85.6727400",“geoplugin_place”:“Boston”,“geoplugin_region”:“Kentucky”,“geoplugin_regionCode”:“KY”,“geoplugin_countryCode”:“US”},“4”:{“geoplugin_latitude”:“33.4415100”,“geoplugin_longitude”:"-94.4196500",“geoplugin_place”:“Boston”,“geoplugin_region”:“Texas”,“geoplugin_regionCode”:“TX”,“geoplugin_countryCode”:“US”},“5”:{“geoplugin_latitude”:“38.5409600”,“geoplugin_longitude”:"-78.1316700",“geoplugin_place”:“Boston”,“geoplugin_region”:“Virginia”,“geoplugin_regionCode”:“VA”,“geoplugin_countryCode”:“US”},“6”:{“geoplugin_latitude”:“42.6289500”,“geoplugin_longitude”:"-78.7375300",“geoplugin_place”:“Boston”,“geoplugin_region”:“New York”,“geoplugin_regionCode”:“NY”,“geoplugin_countryCode”:“US”}}

eg 2:
http://www.geoplugin.net/extras/forward_place.gp?format=json&place=Newcastle&country=GB

Will give

{“geoplugin_status”:200,“0”:{“geoplugin_latitude”:“52.4333300”,“geoplugin_longitude”:"-3.1166700",“geoplugin_place”:“Newcastle”,“geoplugin_region”:“England”,“geoplugin_regionCode”:“ENG”,“geoplugin_countryCode”:“GB”},“1”:{“geoplugin_latitude”:“54.2180400”,“geoplugin_longitude”:"-5.8897900",“geoplugin_place”:“Newcastle”,“geoplugin_region”:“Northern Ireland”,“geoplugin_regionCode”:“NIR”,“geoplugin_countryCode”:“GB”}}

That’s it - I’m out for some partying :slight_smile:

—edit
As I can’t give 3 consecutive replies, I’ll write this here while I can still edit the post!

@Hyperbytes & @mimuk - I’ve been toying for a long time now playing around with OSM and have things in place with their planet pbf file in a spatial DB. The learning curve is extremely steep, but the link to opencage data that @mimuk provided allowed me to find libpostal to allow input in any format to parse backend to street, town, postcode, country etc that is essential in forward geocoding addresses and stuff in a single query globally (if not too many API variables - street=xxx, postcode=yyy, town =zzz etc)

Libpostal gave me the final push to see how OSM can be “easily” implemented into a global forward geocoding API - so consider my teeth sunk into this!

Hope I can provide something useful and very accurate for you guys - @120 free lookups/min, that’s ~35k/day . Hopefully way more useful than opencagedata’s 2.5k/day limit, yet just as accurate as they use OSM also.

3 Likes
#20

@geoplugin how to forward geocode on the fly (houselevel accuracy, switzerland) from an input form before inserting it into mysql? I signed up for opencagedata, but if your service provides same data (OSM) then I could go with geocode also