Bug -- Walkshed not assigned for some records entered vie bulk data interface

[TimReardon]

Some batches of projects entered via bulk data interface were not assigned to a walkshed (e.g., surveys 7792 – 7953 from Thomas Rodman school; and surveys 7617 – 7791 from Elizabeth Carter Brook school)

Diagnose and debug this error.

[TimReardon]

It looks like this same issue may have affected surveys from the Pacheco school (surveys ~10008 - 10341) and Hayden/Mcfadden (surveys ~10941 - 11305)

[demiurg]

I don't think I understand this issue. Surveys can't have walksheds assigned to them, but a school, and a location. Location can be used to place it in a waslkshed of a school. Checking the database, it looks like there are 122 surveys out of 12735 total surveys that don't have a location. I suspect this must be because geolocating streets did not return a location and the point was not placed on the map.

It also looks like all Thomas R Rodman schools, 87 of them, have location and school set.

[TimReardon]

The "survey-child-survey" table, which is used for report generation, has a field called "Shed" (values 0 -4) which indicates the walkshed. This field is used by the R scripts for report generation. In the most recent version of the survey-child-survey table you sent over last month, the Rodman and Carter Brook schools have a valid location but the "shed" field is blank. (not zero, just blank). When is that "Shed" value assigned?

[demiurg]

It looks like the shed is assigned dynamically, at the creation of the result for the query for of survey_child_survey table. It does a geometry search, checking which shed the location of the survey falls into. It can fall outside of the range.

[TimReardon]

Yes, a point can lie outside of any mapped walkshed, but in that case it should be assigned a value of zero. In the cases noted here, there is a valid X-Y location for the survey (coordinates are in the table), the walksheds for the school have been delineated (according to the school page), and the points actually do fall within a walkshed. Plus the error is affecting a large number of sequential surveys, which makes me think there is some bug that is being missed.

[demiurg]

I figured out the problem, which is that a null shed for the 2 mile bike shed was breaking things, worked around it, so the report is generating now. For debugging, I added display of surveys on school page, but because of privacy, only for site staff users. This can be disabled.

[demiurg]

No custom functions, I just had to upgrade to pgrouting 2, because old pgrouting1 functions were no longer working:

http://docs.pgrouting.org/2.0/en/doc/src/installation/index.html#ubuntu-debian

On Fri, Mar 6, 2015 at 12:23 PM, Matt Gardner notifications@github.com wrote:

Hi @demiurg https://github.com/demiurg - thank you for knocking out these issues! I pulled in your changes to my local copy, and most everything looks great.

I'm not sure if this is the relevant issue, but I noticed in this commit here https://github.com/MAPC/myschoolcommute/commit/b0a027a5bb250a911833a32a576a0422459b0199#diff-2a48f35a52701688c99e279e72a9232fR89 that there's possibly a custom SQL function. Would you be able to share custom SQL functions like this so that I can run them locally on my machine?

Much appreciated!

— Reply to this email directly or view it on GitHub https://github.com/MAPC/myschoolcommute/issues/70#issuecomment-77598194.

[allthesignals]

@demiurg Sorry, I missed that part in your comments. I upgraded to 2.* accordingly. Thanks.

Locally, I'm seeing a SQL-related error: Cannot find SRID (900914) in spatial_ref_sys. I'm not sure what this is related to, after some troubleshooting. I'm running postgis 2.0.3. Any thoughts appreciated!

Thanks again.

[demiurg]

I think this is a projection that gets generated by GDAL when it imports a shapefile, such walk paths, can find the projecting in shapefile.prj, but can't find it in spatial_ref_sys. It insert the projection into spatial_ref_sys with 900914 or increments.

To recreate that 'unknown' projection, create temporary table as select statement of the projection, export table as insert statements. Since I had to do it, I have the query:

INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) VALUES (900914, NULL, NULL, 'PROJCS["NAD_1983_StatePlane_Massachusetts_Mainland_FIPS_2001",GEOGCS["GCS_North_American_1983",DATUM["North_American_Datum_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433],AUTHORITY["EPSG","4269"]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",200000.0],PARAMETER["False_Northing",750000.0],PARAMETER["Central_Meridian",-71.5],PARAMETER["Standard_Parallel_1",41.71666666666667],PARAMETER["Standard_Parallel_2",42.68333333333333],PARAMETER["Latitude_Of_Origin",41.0],UNIT["Meter",1.0]]', '+proj=lcc +lat_1=41.71666666666667 +lat_2=42.68333333333333 +lat_0=41 +lon_0=-71.5 +x_0=200000 +y_0=750000 +datum=NAD83 +units=m +no_defs ');

On Fri, Mar 6, 2015 at 1:12 PM, Matt Gardner notifications@github.com wrote:

Sorry, I missed that part in your comments. I upgraded to 2.* accordingly. Thanks.

Locally, I'm seeing a SQL-related error: Cannot find SRID (900914) in spatial_ref_sys. I'm not sure what this is related to, after some troubleshooting. I'm running postgis 2.0.3. Any thoughts appreciated!

Thanks again.

— Reply to this email directly or view it on GitHub https://github.com/MAPC/myschoolcommute/issues/70#issuecomment-77606785.

[demiurg]

I think it's actually important to establish the official equalarea projection for a project such as this and reproject everything into that. I thought originally the walks were in EPSG:26986, but it did this 900914 thing, and the proj4 strings are slightly different. Technically, it doesn't matter now, since both definitions are spatial_ref_sys, and I added transformation from 900914 to 26986 in SQL. But it can be taken out, if they equivalent.

On Fri, Mar 6, 2015 at 1:49 PM, Pavel Dorovskoy pavel.com@gmail.com wrote:

I think this is a projection that gets generated by GDAL when it imports a shapefile, such walk paths, can find the projecting in shapefile.prj, but can't find it in spatial_ref_sys. It insert the projection into spatial_ref_sys with 900914 or increments.

To recreate that 'unknown' projection, create temporary table as select statement of the projection, export table as insert statements. Since I had to do it, I have the query:

INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) VALUES (900914, NULL, NULL, 'PROJCS["NAD_1983_StatePlane_Massachusetts_Mainland_FIPS_2001",GEOGCS["GCS_North_American_1983",DATUM["North_American_Datum_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433],AUTHORITY["EPSG","4269"]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",200000.0],PARAMETER["False_Northing",750000.0],PARAMETER["Central_Meridian",-71.5],PARAMETER["Standard_Parallel_1",41.71666666666667],PARAMETER["Standard_Parallel_2",42.68333333333333],PARAMETER["Latitude_Of_Origin",41.0],UNIT["Meter",1.0]]', '+proj=lcc +lat_1=41.71666666666667 +lat_2=42.68333333333333 +lat_0=41 +lon_0=-71.5 +x_0=200000 +y_0=750000 +datum=NAD83 +units=m +no_defs ');

On Fri, Mar 6, 2015 at 1:12 PM, Matt Gardner notifications@github.com wrote:

Sorry, I missed that part in your comments. I upgraded to 2.* accordingly. Thanks.

Locally, I'm seeing a SQL-related error: Cannot find SRID (900914) in spatial_ref_sys. I'm not sure what this is related to, after some troubleshooting. I'm running postgis 2.0.3. Any thoughts appreciated!

Thanks again.

— Reply to this email directly or view it on GitHub.