Georeferencing a column doesn't work in my local instance of CartoDB

Question

I'm installing a instance of CartoDB in AWS/Ubuntu 12.04, and I'm having a hard time trying to georeference a column with textual addresses.

I know that the data I'm trying to georeference is ok since I uploaded it in an online CartoDB account and it's working there, so it must be a local problem. I've tried with different CartoDB versions, and I get different errors:

v2.9.3 to v2.10.1 -> I upload the file, table is created, then I select 'georeference' in the proper column and seems that the job is processed by resque (progress bar appears in UI), two columns are added (the geom (all values as null) and cartodb_georef_status (all values as false)), but they are not updated later with proper values.

Checking the log, I can see this:

    18:54:42 resque.1  | ** [18:54:42 2014-03-24] 7121: got: (Job{geocodings} | Resque::GeocoderJobs | [{"job_id"=>1}])
    18:54:42 resque.1  | ** [18:54:42 2014-03-24] 7199: resque-1.23.0: Processing geocodings since 1395687282
    18:54:42 resque.1  | ** [18:54:42 2014-03-24] 7121: resque-1.23.0: Forked 7199 at 1395687282
    18:54:44 web.1     | (0.009665s) SELECT * FROM "geocodings" WHERE "id" = 1
    18:54:44 web.1     | (0.003518s) SELECT * FROM "user_tables" WHERE ("user_tables"."id" = 1) LIMIT 1
    18:54:44 web.1     | (0.004384s) SELECT * FROM "users" WHERE ("users"."id" = 1) LIMIT 1
    18:54:44 web.1     | [pool] Creating a new connection for cartodb_dev_user_1_db:postgres (1)
    18:54:44 web.1     | (0.005848s) BEGIN
    18:54:44 web.1     | (0.000490s) UPDATE "geocodings" SET "updated_at" = '2014-03-24 18:54:43.110957+0000', "state" = 'started' WHERE ("id" = 1)
    18:54:44 web.1     | (0.002168s) COMMIT
    18:54:44 web.1     | (0.000164s) SET standard_conforming_strings = ON
    18:54:44 web.1     | (0.000165s) SET client_min_messages = 'WARNING'
    18:54:44 web.1     | (0.000161s) SET DateStyle = 'ISO'
    18:54:44 web.1     | (0.050511s) 
    18:54:44 web.1     |         ALTER TABLE map_03_2 
    18:54:44 web.1     |         ADD COLUMN cartodb_georef_status BOOLEAN DEFAULT FALSE
    18:54:44 web.1     |       
    18:54:44 web.1     | (0.001902s) 
    18:54:44 web.1     |             SELECT md5(trim(both from regexp_replace(regexp_replace(concat(address), E'[\n\r]+', ' ', 'g'), E'"', '', 'g'))) AS searchtext
    18:54:44 web.1     |             FROM map_03_2
    18:54:44 web.1     |             WHERE cartodb_georef_status IS false OR cartodb_georef_status IS NULL
    18:54:44 web.1     |             GROUP BY searchtext
    18:54:44 web.1     |             LIMIT 10 OFFSET 0
    18:54:44 web.1     |         
    18:54:44 web.1     | (0.000194s) DROP TABLE IF EXISTS geocoding_cache_1395687283
    18:54:44 web.1     | [Rollbar] Scheduling payload
    18:54:44 web.1     | [Rollbar] Sending payload
    18:54:44 web.1     | [Rollbar] Got unexpected status code from Rollbar api: 401
    18:54:44 web.1     | [Rollbar] Response: {
    18:54:44 web.1     |   "err": 1,
    18:54:44 web.1     |   "message": "unauthorized"
    18:54:44 web.1     | }
    18:54:44 web.1     | [Rollbar] Details: https://rollbar.com/instance/uuid?uuid=3a66799c-34fa-4733-bf78-0a251fd5bf24 (only available if report was successful)
    18:54:44 web.1     | (0.000767s) COPY (
    18:54:44 web.1     |           SELECT trim(both from regexp_replace(regexp_replace(concat(address), E'[\n\r]+', ' ', 'g'), E'"', '', 'g')) as recId, trim(both from regexp_replace(regexp_replace(concat(address), E'[\n\r]+', ' ', 'g'), E'"', '', 'g')) as searchText 
    18:54:44 web.1     |           FROM map_03_2
    18:54:44 web.1     |           WHERE cartodb_georef_status IS FALSE OR cartodb_georef_status IS NULL
    18:54:44 web.1     |           GROUP BY recId
    18:54:44 web.1     |           LIMIT 1000000
    18:54:44 web.1     |       ) TO STDOUT (FORMAT csv, HEADER)
    18:54:44 web.1     | ETHON: Libcurl initialized
    18:54:44 web.1     | ETHON: performed EASY url= response_code=0 return_code=url_malformat total_time=0.0
    18:54:44 web.1     | ETHON: performed EASY url= response_code=0 return_code=url_malformat total_time=0.0
    18:54:44 web.1     | 
    18:54:44 web.1     | 
    18:54:44 web.1     | Started GET "/api/v1/geocodings/1" for (IP) at 2014-03-24 18:54:44 +0000
    18:54:44 web.1     | Processing by Api::Json::GeocodingsController#show as JSON
    18:54:44 web.1     |   Parameters: {"id"=>"1"}
    18:54:44 web.1     | (0.017074s) SELECT * FROM "users" WHERE ("username" = 'ec2-54-185-162-75') LIMIT 1
    18:54:44 web.1     | (0.014046s) SELECT * FROM "geocodings" WHERE (("geocodings"."user_id" = 1) AND ("id" = '1')) ORDER BY "created_at" DESC LIMIT 1
    18:54:44 web.1     | (0.004323s) SELECT * FROM "user_tables" WHERE ("user_tables"."id" = 1) LIMIT 1
    18:54:44 web.1     | (0.000345s) SELECT * FROM "users" WHERE ("users"."id" = 1) LIMIT 1
    18:54:44 web.1     | [pool] Found a connection for cartodb_dev_user_1_db:postgres (2)
    18:54:44 web.1     | Completed 200 OK in 286ms (Views: 0.8ms | Models: 0.0ms)
    18:54:44 resque.1  | 2/3
    18:54:45 web.1     | (0.008280s) BEGIN
    18:54:45 web.1     | (0.003983s) UPDATE "geocodings" SET "updated_at" = '2014-03-24 18:54:44.365433+0000', "remote_id" = 'c3489b58b38511e38e7722000aed033e' WHERE ("id" = 1)
    18:54:45 web.1     | (0.002151s) COMMIT
    18:54:45 web.1     | (0.000305s) BEGIN
    18:54:45 web.1     | (0.000401s) UPDATE "geocodings" SET "total_rows" = 3, "processed_rows" = 2, "updated_at" = '2014-03-24 18:54:44.530939+0000', "state" = 'completed' WHERE ("id" = 1)
    18:54:45 web.1     | (0.001879s) COMMIT
    18:54:45 web.1     | 
    18:54:45 web.1     | 
    18:54:45 web.1     | Started GET "/api/v1/geocodings/1" for (IP) at 2014-03-24 18:54:45 +0000
    18:54:45 web.1     | Processing by Api::Json::GeocodingsController#show as JSON
    18:54:45 web.1     |   Parameters: {"id"=>"1"}
    18:54:45 web.1     | (0.002891s) SELECT * FROM "users" WHERE ("username" = 'ec2-54-185-162-75') LIMIT 1
    18:54:45 web.1     | (0.000345s) SELECT * FROM "geocodings" WHERE (("geocodings"."user_id" = 1) AND ("id" = '1')) ORDER BY "created_at" DESC LIMIT 1
    18:54:45 web.1     | (0.003857s) SELECT * FROM "user_tables" WHERE ("user_tables"."id" = 1) LIMIT 1
    18:54:45 web.1     | (0.000350s) SELECT * FROM "users" WHERE ("users"."id" = 1) LIMIT 1
    18:54:45 web.1     | [pool] Found a connection for cartodb_dev_user_1_db:postgres (2)
    18:54:45 web.1     | Completed 200 OK in 148ms (Views: 0.9ms | Models: 0.0ms)
    18:54:47 web.1     | (0.000256s) BEGIN
    18:54:47 web.1     | (0.000428s) UPDATE "geocodings" SET "updated_at" = '2014-03-24 18:54:46.543169+0000', "cache_hits" = NULL WHERE ("id" = 1)
    18:54:47 web.1     | (0.001688s) COMMIT
    18:54:47 web.1     | 
    18:54:47 web.1     | 
    18:54:47 web.1     | Started GET "/api/v1/geocodings/1" for (IP) at 2014-03-24 18:54:47 +0000
    18:54:47 web.1     | Processing by Api::Json::GeocodingsController#show as JSON
    18:54:47 web.1     |   Parameters: {"id"=>"1"}
    18:54:47 web.1     | (0.016115s) SELECT * FROM "users" WHERE ("username" = 'ec2-54-185-162-75') LIMIT 1
    18:54:47 web.1     | (0.008763s) SELECT * FROM "geocodings" WHERE (("geocodings"."user_id" = 1) AND ("id" = '1')) ORDER BY "created_at" DESC LIMIT 1
    18:54:47 web.1     | (0.000302s) SELECT * FROM "user_tables" WHERE ("user_tables"."id" = 1) LIMIT 1
    18:54:47 web.1     | (0.000353s) SELECT * FROM "users" WHERE ("users"."id" = 1) LIMIT 1
    18:54:47 web.1     | [pool] Found a connection for cartodb_dev_user_1_db:postgres (2)
    18:54:47 web.1     | PG::Error: ERROR:  relation "cdb.geo_c3489b58b38511e38e7722000aed033e" does not exist
    18:54:47 web.1     | LINE 1: SELECT NULL FROM "cdb.geo_c3489b58b38511e38e7722000aed033e" ...
    18:54:47 web.1     |                          ^: SELECT NULL FROM "cdb.geo_c3489b58b38511e38e7722000aed033e" LIMIT 1
    18:54:47 web.1     | (0.013806s) 
    18:54:47 web.1     |         CREATE TABLE cdb.geo_c3489b58b38511e38e7722000aed033e (
    18:54:47 web.1     |           recId text, 
    18:54:47 web.1     |           SeqNumber int, 
    18:54:47 web.1     |           seqLength int, 
    18:54:47 web.1     |           displayLatitude float, 
    18:54:47 web.1     |           displayLongitude float
    18:54:47 web.1     |         );
    18:54:47 web.1     | PG::Error: ERROR:  relation "cdb.geo_c3489b58b38511e38e7722000aed033e" does not exist
    18:54:47 web.1     | LINE 1: SELECT NULL FROM "cdb.geo_c3489b58b38511e38e7722000aed033e" ...
    18:54:47 web.1     |                          ^: SELECT NULL FROM "cdb.geo_c3489b58b38511e38e7722000aed033e" LIMIT 1
    18:54:47 web.1     | (0.000213s) COPY cdb.geo_c3489b58b38511e38e7722000aed033e FROM STDIN (FORMAT csv)
    18:54:47 web.1     | PG::Error: ERROR:  relation "cdb.geo_c3489b58b38511e38e7722000aed033e" does not exist
    18:54:47 web.1     | LINE 1: SELECT NULL FROM "cdb.geo_c3489b58b38511e38e7722000aed033e" ...
    18:54:47 web.1     |                          ^: SELECT NULL FROM "cdb.geo_c3489b58b38511e38e7722000aed033e" LIMIT 1
    18:54:47 web.1     | (0.102498s) 
    18:54:47 web.1     |         UPDATE map_03_2 AS dest
    18:54:47 web.1     |         SET the_geom = ST_GeomFromText(
    18:54:47 web.1     |             'POINT(' || orig.displayLongitude || ' ' ||
    18:54:47 web.1     |               orig.displayLatitude || ')', 4326
    18:54:47 web.1     |             ),
    18:54:47 web.1     |             cartodb_georef_status = true
    18:54:47 web.1     |         FROM cdb.geo_c3489b58b38511e38e7722000aed033e AS orig
    18:54:47 web.1     |         WHERE trim(both from regexp_replace(regexp_replace(concat(address), E'[\n\r]+', ' ', 'g'), E'"', '', 'g')) = orig.recId
    18:54:47 web.1     |       
    18:54:47 web.1     | (0.000225s) DROP TABLE IF EXISTS geocoding_cache_1395687283
    18:54:47 web.1     | [Rollbar] Scheduling payload
    18:54:47 web.1     | [Rollbar] Sending payload
    18:54:47 web.1     | Completed 200 OK in 185ms (Views: 0.9ms | Models: 0.0ms)
    18:54:47 resque.1  | ** [18:54:47 2014-03-24] 7199: done: (Job{geocodings} | Resque::GeocoderJobs | [{"job_id"=>1}])

v2.11.0, master -> I upload the file, table is created, then I select 'georeference' in the proper column and seems that an error happens even before the job is processed by resque (progress bar doesn't appear in UI).

Checking the log, I can see this:

Started POST "/api/v1/geocodings" for (IP) at 2014-03-24 18:11:22 +0000
18:11:22 web.1     | Processing by Api::Json::GeocodingsController#create as JSON
18:11:22 web.1     |   Parameters: {"formatter"=>"{address}", "table_name"=>"map_03_2"}
18:11:22 web.1     | (0.000638s) SELECT * FROM "users" WHERE ("username" = 'ec2-54-185-162-75') LIMIT 1
18:11:22 web.1     | (0.007037s) SELECT * FROM "user_tables" WHERE (("user_id" = 'b249cedd-dc33-49eb-86f1-a53f1ca17731') AND ("name" = 'map_03_2')) ORDER BY "id" DESC LIMIT 1
18:11:22 web.1     | (0.012165s) SELECT "pg_attribute"."attname" AS "name", CAST("pg_attribute"."atttypid" AS integer) AS "oid", format_type("pg_type"."oid", "pg_attribute"."atttypmod") AS "db_type", pg_get_expr("pg_attrdef"."adbin", "pg_class"."oid") AS "default", NOT "pg_attribute"."attnotnull" AS "allow_null", COALESCE(("pg_attribute"."attnum" = ANY("pg_index"."indkey")), false) AS "primary_key", "pg_namespace"."nspname" FROM "pg_class" INNER JOIN "pg_attribute" ON ("pg_attribute"."attrelid" = "pg_class"."oid") INNER JOIN "pg_type" ON ("pg_type"."oid" = "pg_attribute"."atttypid") INNER JOIN "pg_namespace" ON ("pg_namespace"."oid" = "pg_class"."relnamespace") LEFT OUTER JOIN "pg_attrdef" ON (("pg_attrdef"."adrelid" = "pg_class"."oid") AND ("pg_attrdef"."adnum" = "pg_attribute"."attnum")) LEFT OUTER JOIN "pg_index" ON (("pg_index"."indrelid" = "pg_class"."oid") AND ("pg_index"."indisprimary" IS TRUE)) WHERE (("pg_attribute"."attisdropped" IS FALSE) AND ("pg_attribute"."attnum" > 0) AND ("pg_class"."relname" = 'assets') AND ("pg_namespace"."nspname" !~* 'pg_*|information_schema')) ORDER BY "pg_attribute"."attnum"
18:11:22 web.1     | Completed 422 Unprocessable Entity in 269ms (Views: 0.3ms | Models: 0.0ms)

v2.11.1 -> I upload the file, but I cannot view the rows in the UI (I got an error).

Checking the log, I can see this:

17:30:32 web.1     | Started GET "/api/v1/tables/map_03_2/records?rows_per_page=40&page=0&mode=asc&order_by=cartodb_id&filter_column=&filter_value=&api_key=66fc98d3bddf79a6c0938f4282a2140fd9b00360" for (IP) at 2014-03-24 17:30:32 +0000
17:30:32 web.1     | [Rollbar] Reporting exception: The action 'index' could not be found for Api::Json::RecordsController
17:30:32 web.1     | (0.000598s) SELECT * FROM "users" WHERE ("username" = 'ec2-54-185-162-75') LIMIT 1
17:30:32 web.1     | [Rollbar] Exception not reported because Rollbar is disabled
17:30:32 web.1     | 
17:30:32 web.1     | AbstractController::ActionNotFound (The action 'index' could not be found for Api::Json::RecordsController):

You need to get an API key from Nokia and add it to your app_config.yml — javisantana, Mar 25, 2014 at 11:23

iriberri · Accepted Answer · 2015-06-19 14:38:40Z

1

As @javisantana answered last year in his comment, you need to set up the Nokia Here geocoder credentials.

You need to get an API key from Nokia and add it to your app_config.yml

answered Jun 19, 2015 at 14:38

iriberri

4,8022 gold badges14 silver badges27 bronze badges

Add a comment |

Stack Exchange Network

Georeferencing a column doesn't work in my local instance of CartoDB

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
carto
geocoding
or ask your own question.

Hot Network Questions

Georeferencing a column doesn't work in my local instance of CartoDB

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged cartogeocoding or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
carto
geocoding
or ask your own question.