Lime Data Download Fails to Produce Working Shapefile

[mzagaja]

Describe the bug

The OD pairs presents an error that the data is unreadable.

Loading this Lime dataset in QGIS yields an empty map including an empty attribute layer.

Steps to reproduce
Walk us through how you revealed the bug.

1. Go to https://datacommon.mapc.org/browser/datasets/402
2. Click on .shp
3. Open the shapefile in your favorite GIS software.
4. See lack of data.

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: MacOS 10.15.4
• Browser Safari
• Version 13.1
• QGIS 3.12

[mzagaja]

It looks like the request to the server is prefixing the database name as part of the table name parameter:

I, [2020-05-28T11:54:38.594946 #25704]  INFO -- : [c6c51845-f0a9-4f36-a4a7-95aa3b0c760f] Processing by DownloadsController#shapefile as HTML
I, [2020-05-28T11:54:38.594993 #25704]  INFO -- : [c6c51845-f0a9-4f36-a4a7-95aa3b0c760f]   Parameters: {"table"=>"gisdata.mapc.trans_lime_od_pairs_g250", "database"=>"gisdata"}
I, [2020-05-28T11:54:38.595488 #25704]  INFO -- : [c6c51845-f0a9-4f36-a4a7-95aa3b0c760f] Sent file public/cache/export-gisdata.mapc.trans_lime_od_pairs_g250.zip (0.2ms)
I, [2020-05-28T11:54:38.595604 #25704]  INFO -- : [c6c51845-f0a9-4f36-a4a7-95aa3b0c760f] Completed 200 OK in 1ms (ActiveRecord: 0.0ms | Allocations: 251)
I, [2020-05-28T11:55:29.479023 #25704]  INFO -- : [68ca434f-a91a-42be-a2f7-71fca272b238] Started HEAD "/browser/datasets/402" for 217.182.175.162 at 2020-05-28 11:55:29 -0400
I, [2020-05-28T11:55:29.479701 #25704]  INFO -- : [68ca434f-a91a-42be-a2f7-71fca272b238] Processing by PagesController#index as HTML

Another possibility is

ogr2ogr -f 'ESRI Shapefile' tmp/cache/lime_test.shp PG:'host=ip-10-0-0-76.ec2.internal port=5432 user=HIDDEN dbname=HIDDEN password=HIDDEN' -sql 'SELECT *,sde.ST_AsText(shape) FROM gisdata.mapc.trans_lime_od_pairs_g250' -skipfailures
ERROR 1: ERROR:  function getsrid(cstring) does not exist
LINE 1: SELECT getsrid("st_astext") FROM(SELECT *,sde.ST_AsText(shap...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

ERROR 1: ERROR:  function getsrid(cstring) does not exist
LINE 1: SELECT getsrid("st_astext") FROM(SELECT *,sde.ST_AsText(shap...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

that said file sizes look the same with or without gisdata

-rw-rw-r--   1 datacommon datacommon 37381442 May 29 15:54 lime_test_2.dbf
-rw-rw-r--   1 datacommon datacommon 22695284 May 29 15:54 lime_test_2.shp
-rw-rw-r--   1 datacommon datacommon  1335140 May 29 15:54 lime_test_2.shx
-rw-rw-r--   1 datacommon datacommon 37381442 May 29 15:53 lime_test.dbf
-rw-rw-r--   1 datacommon datacommon 22695284 May 29 15:53 lime_test.shp
-rw-rw-r--   1 datacommon datacommon  1335140 May 29 15:53 lime_test.shx

versus

-rw-r--r-- 1 datacommon datacommon        495 Apr 13 18:09 export-gisdata.mapc.trans_lime_od_by_q_g250.zip
-rw-rw-rw- 1 datacommon datacommon   37381442 Jan 22 12:14 export-gisdata.mapc.trans_lime_od_pairs_g250.dbf
-rw-rw-rw- 1 datacommon datacommon   22695284 Jan 22 12:14 export-gisdata.mapc.trans_lime_od_pairs_g250.shp
-rw-rw-rw- 1 datacommon datacommon    1335140 Jan 22 12:14 export-gisdata.mapc.trans_lime_od_pairs_g250.shx

[mzagaja]

Possibly related to OSGeo/gdal#1692. Next steps are to add the ubuntu gis PPA to live.mapc.org and upgrade GDAL (for the ogr2ogr utility). Going to try this on Monday since it may break the existing app in other ways due to being a major version bump.

[mzagaja]

I added the Ubuntu GIS PPA to bump GDAL to version 2.4.2, deleted the .zip file and re-ran it. It seems to have worked.

[mzagaja]

Closing this as fixed per #306 (comment) since we haven't heard back and the fix worked for me.