Geocoding Script

I am a BA type (no programming expertise) helping a NFP NGO create a Food website.

I have recently converted from MySQL to Maria (10.7, X64) and use Heidi (11.3.0.6295) as the interface.

In MySQL, I used the following script to geo-code 107K Providores (farmers, grower, producers, etc):

Geo-code

ALTER TABLE prov_au ADD Location Point:

ALTER TABLE prov_au MODIFY Location POINT NOT NULL; and it took a few seconds to create the Geometry field in the Location field.

This is used for mapping, calculating food miles, etc.

I did manage to geo-code a smaller Maria DB (11K records) with a script similar to this:

ALTER TABLE vic_rural ADD Location Point;

INSERT INTO vic_rural VALUES (GeomFromText ('POINT(longitude, latitude)'));

Not sure how, but it (or some script similar worked - and without a 'where' clause - see below.

But have not been able to replicate this, as Heidi now requires me to add a 'where' clause somewhere in the Query script, which is beyond my knowledge.

Any ideas?

Can you add a screenshot of how HeidiSQL requires you to add a WHERE clause?

Hi Ansgar, Regret I have not been able to replicate the "where' clause problem in HeidiSQL, but

Using "INSERT INTO xyz.MariaDB VALUES (GEOMFROMTEXT ('Point(longitude, latitude)')); results in "SQL Error (1136:Column counts does not match value count at row 1"

I have searched Google for what this means but cannot find anything definitive.

Any clues?

Thanks Chris

Have attached 3 images from my development PC, along with some scripts attempted.

This exercise was easy in MySQl, but beyond me (a BA) in HeidiSQL.

If anyone can help, would be greatly appreciated.

Although this is for a startup NFP NGO, we are happy to pay for help.

Your INSERT only contains one value, and the table has 40 or 50 columns. The server cannot guess in which column you want to insert the value.

Solution is to add the location column name in the INSERT:

INSERT INTO au_prov (location) VALUES (GEOMFROMTEXT...

Thanks for the clue, seems obvious now, but still no POINT value added, as per attachment.

Will be trying other scripts, based on this clue.

Did manage to Geometry code an 11K DB about 3 months ago (cannot remember how), so know it is possible.

Your INSERT command did insert a row with a value, as the log panel says "Affected rows: 1".

If you want to update existing rows, you probably want an UPDATE command:

UPDATE au_prov SET location = GEOMFROMTEXT ('Point(longitude, latitude)');

Many thanks, eventually got this to work - UPDATE au_prov SET Location = POINT(longitude, latitude);