Import json parameters from external url to generate a map using openstreetmap

(2 posts) (2 voices)
  • Started 2 months ago by Alex35580
  • Latest reply from myDBR Team

Tags:

No tags yet.

  1. Alex35580, Member

    Hi all,
    I'm a mydbr newbie, even if I like this amazing tool, I have some issue for my first import. I try to generate an openstreet map using parameters from an external url (ip2location) in order to geolocalize an IP.
    From a first list of IP addresses, I use the report command using :

    SELECT 'dbr.report', 'sp_DBR_IP_LOCATION', 'inIP=[LAST_IP]';

    The report procedure is very simple

    DROP PROCEDURE IF EXISTS sp_DBR_IP_LOCATION $$ CREATE PROCEDURE `sp_DBR_IP_LOCATION`(inIP varchar(15)) BEGIN

    select 'dbr.css', 'div.title { background-color: #f4f4f4; color: black; text-align: center; vertical-align: middle; font-weight: bold; font-family: "Roboto-Light";';

    select 'dbr.import.options', 'format', 'json'; select 'dbr.import', 'sp_DBR_import_IP_JSON_do' ,concat('https://api.ip2location.com/?ip=', inIP, '&key=demo&package=WS3&format=json');

    END $$

    When I put the url (https://api.ip2location.com/?ip=80.215.47.235&key=demo&package=WS3&format=json) in my browser, I get a perfect json message :

    {"country_code":"FR","country_name":"France","region_name":"Centre-Val de Loire","city_name":"Orleans"}

    But during the json import I have the error message : Fatal error: Cannot use object of type stdClass as array in /var/www/tools/mydbr/lib/import.php on line 353

    DROP PROCEDURE IF EXISTS sp_DBR_import_IP_JSON_do $$ CREATE PROCEDURE `sp_DBR_import_IP_JSON_do`(country_code varchar(100), country_name varchar(100), region_name varchar(100), city_name varchar(100)) BEGIN select 'dbr.openstreetmap', 'address'; select city_name, concat(city_name, ', ',country_name); END $$

    Can someone can help me please ?

    Thanks for your help and have a great day

    Posted 2 months ago #
  2. myDBR Team, Key Master

    While your approach is quite good, the import is designed to work bit differently. The main purpose of the import is to connect to data source and import array of data (usually multiple rows).

    1) The IP2Location's API returns single JSON object instead of array of lines and the error you see comes from the fact that myDBR expects an array objects while there is only one single object. We've now added support for this use case and if you update myDBR to the latest build, using IP2Location's API as data source is possible.
    2) The dbr.import's procedure (sp_DBR_import_IP_JSON_do) is not a report in itself and will not handle the dbr.xxx commands. The purpose of the procedure is to handle the incoming data (usually storing the data to database). In your case also it would be better to store the fetched geodata into the database so you do not need to refetch (and consume your IP2Location's account).

    If you use the latest build, you can do all this much easier with the ip2location extension does the IP address conversion for you. Take a look at the documentation.

    --
    myDBR Team

    Posted 2 months ago #

Reply

You must log in to post.