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):
