1

right now I'm working on a project where I'm trying to visualize internet coverage in new york city. I've downloaded the fcc's csv that details the internet coverage of the united states and parsed through it using java to get all of the data that's only pertaining to new york city. I did that by only choosing data rows that contained the proper census codes of the 5 new york city counties (the five boroughs). Here's the problem. The only geographically linked data that the fcc's csv has is the census codes, and right now, I'm having trouble linking the data to geographic locations on a map. I have a geojson file that has the geodata that goes with each census code in nyc, but I don't know how to parse and edit geojson files in java. This is the format of the geojson I have:

{
   "type": "FeatureCollection",
   "features": [
      {
         "type": "Feature",
         "id": 0,
         "properties": {
            "OBJECTID": 2038,
            "STATEFP": "36",
            "COUNTYFP": "081",
            "TRACTCE": "014000",
            "BLKGRPCE": "1",
            "GEOID": "360810140001",
            "NAMELSAD": "Block Group 1",
            "MTFCC": "G5030",
            "FUNCSTAT": "S",
            "ALAND": 113131,
            "AWATER": 0,
            "INTPTLAT": "+40.7030341",
            "INTPTLON": "-073.8279064",
            "geo_id": "15000US360810140001",
            "OBJECTID_1": 10466,
            "GEOID_1": "15000US360810140001",
            "SHAPE_Leng": 0.02086447918,
            "SHAPE_Area": 0.00001205464
         },
         "geometry": {
            "type": "Polygon",
            "coordinates": [
               [
                  [
                     -73.82266,
                     40.703585
                  ],
                  [
                     -73.83152,
                     40.70139
                  ],
                  [
                     -73.83213,
                     40.702794
                  ],
                  [
                     -73.82576,
                     40.704428
                  ],
                  [
                     -73.82246,
                     40.703656
                  ],
                  [
                     -73.82266,
                     40.703585
                  ]
               ]
            ]
         }
      },

The way I imagine doing this is by selecting the first geojson object, and then using that code to search through the csv, or by selecting the first csv line, and then searching for the same census code in my geojson. That way is obviously really inefficient. Is a better solution to somehow sort the csv based on the numerical value of the census code, and then read down starting with geojson? If the csv is ordered, I can write a binary search algorithm to do that.

To sum it up: I need help with ordering a csv based on one column's numerical value, and help with reading and editing a geojson file. I would appreciate all help.

Here is the code I was trying to use to pair the values (I had converted the geojson to csv, but I found that when converting from csv to geojson, no online code I have found to do so works correctly.

public static void main(String args[]) throws FileNotFoundException{
  // Construct a BufferedReader object from the input file
    BufferedReader r = new BufferedReader(new FileReader("newyorkcitydistilled.csv"));
    BufferedReader cs = new BufferedReader(new FileReader("convertcsv.csv"));
    String[] linedata;
    String[] linedata2;
    PrintWriter pw = new PrintWriter(new File("nycdistilledgeo2.csv"));
    String line;
    String line2;
    int it = 0;
  try{
//prime the geodata

//NOTE TO SELF!!! THE CSV FOR CONVERT NEETS TO BE REFORMATTED BECAUSE OF THE FIRST THREE COLUMNS
      line2 = cs.readLine();
      linedata2 = line2.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)", -1);

    while ((line = r.readLine()) != null) {
          if (line.isEmpty()) {
              break;}
          it++;
          if(it % 100 == 0)
          System.out.println(it);
          linedata = line.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)", -1);
      //    System.out.print(line2);
//while the two lines being looked at dont fit the matching criteria, keep looking through the convertcsv
      while((boroComp(linedata[9].substring(2,5),linedata2[4].substring(0,1)) && linedata[9].substring(5,15).equals(linedata2[4].substring(1,11))) == false){
            line2 = cs.readLine();
            try{
            linedata2 = line2.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)", -1);
        //    System.out.println(linedata2[4]);
          }catch(NullPointerException e){
        //    System.out.println("caught");
            break;
          }
            //somehow before it hits the last line of the csv, it needs to just say no match and move on

//need to restart the progress on readline for CS every time a correct comparison is made
        }
      //  System.out.println("not broken");
//if it is true, add the new line in
    //  System.out.println(line2);
  //    System.out.println(linedata2[0] + "," + linedata2[1] + "," + linedata2[2] + "," + linedata2[5]);
      //  System.out.println(line + linedata2[0] + linedata2[1] + linedata2[2] + linedata2[5]);
        pw.write(line + "," + linedata2[0] + "," + linedata2[1] + "," + linedata2[2] + "," + linedata2[5] + "\n");
//reset the buffered reader
        cs.close();
        cs = new BufferedReader(new FileReader("convertcsv.csv"));
//reprime the String
        line2 = cs.readLine();
        linedata2 = line2.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)", -1);
      }
        r.close();
        cs.close();
}
catch( IOException ioException ) {
    System.out.println("Exception: "+ioException);
}

pw.flush();
}
}

Here's a line of my csv data, sorted from the fcc

  • 1
    what is your code ? we can't help you with your code with only that. – LoneWanderer Nov 05 '18 at 21:53
  • @LoneWanderer I added my code – user10610135 Nov 05 '18 at 22:03
  • Parsing json by hand is unnecessary exercise here, you can use libraries like jackson for that. Same with CSVs, then just load your objects into memory and then run your business logic on them. – Adam Kotwasinski Nov 05 '18 at 22:08
  • 1
    Hey @user10610135, why do you need to convert the geojson to a csv? And if the geojson is linked to the counties in nyc then all you need to do match the geometry data to the county. I assume the real problem is "I have a geojson that covers the whole county and internet coverage metrics that have no geo-data. How can I find a tie the metrics to the geojson?" Is that correct or is there something I'm messing? – Thatalent Nov 05 '18 at 22:15
  • @Thatalent yea, I need to match the geometry data to the county. There are multiple data points that go under each county as well. I have a geojson that covers only NYC, and I have internet coverage metrics that do have a link:unique census codes. Both the geojson and csv files have the census code as an attribute. I'm not sure how to combine them efficiently. Also not sure how to account for multiple data points in one geographic location. – user10610135 Nov 05 '18 at 22:16
  • I managed to sort the CSV, so I can at least use a binary search algo to speed up my searching times – user10610135 Nov 05 '18 at 22:19
  • Okay a few more questions. 1. what do the metrics look like? You might not be able to tie the data correctly without some math or I might be over thinking your problem. 2. Can your code know the list of codes before hand? If you know the code before hand and only need the nyc metrics from the csv, then use a map that uses the code as the key and an object that holds the metrics as the value. Maps have access of O(1) so making a map of should take O(n) time without sorting. If the code can't then you might better with a map of all the metrics. – Thatalent Nov 05 '18 at 22:28
  • @Thatalent I added a screenshot of what the csv looks like. – user10610135 Nov 05 '18 at 23:23
  • 1
    From your snap shot, it looks like the filed `GEOID` from the geodata is linked to the block code from the csv. the problem I see is that block code is more detailed than the GEOID because the block code specifies the block (a section of the block group and a 15 digit code) and the geodata only has the geometry of the block group (12 digit code). Here's a link of that breaks down the 15 digit code http://sainsb.github.io/2013/05/09/census-fips-code-breakdown/ and here's a link about parsing json in java https://stackoverflow.com/a/31743324/9240742. – Thatalent Nov 06 '18 at 16:05
  • If you parse the json, make a class to represent each block group (county) and use a map to store instances of that class with the census code as the key, you'll be good. – Thatalent Nov 06 '18 at 16:06

0 Answers0