Subscribe to this thread
Home - General / All posts - geocoding insert value in field occur or not !!
249 post(s)
#11-Jan-18 15:30


i use this query to insert data thatcome from geocoding service in new table using 3 custom row latlon lat and long instead one column of type Geom .

SQL9 mdf8

SELECT [mfd_id],[Name], [adress 2],

GeocodeAddress([google_gcode],[adress 2]) AS [longlat],

VectorValue(GeocodeAddress([google_gcode],[adress 2]), 1) AS [lat],

VectorValue(GeocodeAddress([google_gcode],[adress 2]), 0) AS [lon]

   INTO [adress_google2_latlong]

   FROM [adress_paris];

here the result . It is strange if longlat OR lat/long have a value i should have value for each colum latlong , lat , long but it is not the case : i have NULL value !!

if NULL value appear twice is the same row that mean geocoding fail perhaps in relation with formating string !?

any clue about one NULL value that appear in some row ?


N§B in SQL9 placeholder , bold work but it is a shame we can change color of text inside SQL9 placeholder using in raw mode <p style="color:red;"> text </p>


249 post(s)
#11-Jan-18 15:58


another question if i want to use the transform gui tool i can 't use lat AND long as an input since transfrom let us select work only on one Column ( input select item support only selection of one item !!) ? so i need to convert the longlat matrix OR lat lon column text to geom vefore use transform gui tool / SQL wizard !!

i think we have to go directly to SQL Wizard in this case !!


click run the blue button label "run code snippet" to test the code ( multi selection )

perhaps a pop up to show which items are active / Selected


249 post(s)
#11-Jan-18 16:43

if table don't contain geom type column then we can't process using GUI i mean contextual menu "New Drawing even we have 2 columns ( lat and long ) or on column latlong matrix [lat,long]. click on one of the the 2 buttons "create drawing " or "edit Query " do nothing !! .



4,422 post(s)
#12-Jan-18 05:40

click on one of the the 2 buttons "create drawing " or "edit Query " do nothing !! .

I am not sure why you are surprised. I don't think it is because you expect a drawing could be created without a geom in the table, since that is well covered in the basic Tables and Drawings topics.

I suppose you might be surprised that a dialog would silently fail instead of raising an error message such as "Whoa! Ain't no geom field in that table, partner! This ain't gonna' work!" I agree with that, since in a perfect world, 9 would have an error message for every possible request that could not be fulfilled and dialogs would not even be enabled if some advance checking was not able.

But, you know how it goes... it is not a perfect world and we make tradeoffs. For now I like the tradeoff 9 makes in terms of seeking to provide maximum possible capabilities for those who are willing to invest into learning the system, and who don't mind a puzzle every now and then which lets them know they might have overlooked something. For now, at a fraction of the effort required to anticipate all possible errors you can get a far more capable system.

Over time we can move that balance away from a focus on power and capabilities into more predictive error messaging and dialog contexting.


7,444 post(s)
#12-Jan-18 10:22

You are attempting to geocode every address three times - to get both lat and lon, to get only lat and to get only lon. Do not do this. Geocode only once to get both lat and lon, and get the rest from that. Multiple attempts to geocode the same address might fail due to connection issues or, say, because the server might think that the requester is either malicious or buggy.

In general, when geocoding using a web service, you should prepare for some of the responses being invalid. It is best to do the process incrementally - for example, record successful results, keep unsuccessful results as NULLs, and only issue geocoding requests for records that have not yet been geocoded and have NULLs. This way you can run the (UPDATE) query once, then run it again to try and geocode what failed to geocode on the first pass for whatever reason, etc.


4,422 post(s)
#12-Jan-18 11:12

You are attempting to geocode every address three times

I missed that. Getting NULL after geocoding the same thing more than once is an effect that is explicitly warned about in the Example: Street Address Geocoding topic, both in a tech tip (mentions an alternate way to the same thing) and in the Notes:

Caution:The query above that runs the GeocodAddressfunction twice, once to then extract a latitude and once to then extract a longitude is not efficient since in both cases we are getting a latitude and longitude. It is presented as written to show the idea in a very simple way. It would be smarter to simply save the latitude and longitude values received from running it once and then extracting from there. Running the GeocodAddressfunction twice in short succession can also anger Google, which might return a NULL for the latitude or the longitude to show its displeasure at asking the same thing twice. We can get around all that by creating a geom and then using the procedure in the Example: Create a Geocoded Table from a Drawingtopic to generate separate Latitude and Longitude fields from the geom.


NULLs - When a geocoding server returns a NULL for an address that usually means one of several things:

  • The geocoding server cannot find the street address in its database.

  • You have asked for too many geocodes in too short a time.

  • You have somehow otherwise gone beyond the bounds of what the server allows for use, such as number of records per minute, per hour, or per day.

  • The geocoding server encountered an error, is overwhelmed by other requests or otherwise has a problem.

249 post(s)
#13-Jan-18 10:05

Thank's à lot. It s confirm What i was thinking. I sometime use tools not in the logical way ( m'y way).

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