Can I do this with DataTables? If so, how?

Can I do this with DataTables? If so, how?

JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

I am making a mapping tool using Leaflet javaScript and using Ajax/jQuery to pull information directly from GeoServer. Once a user selects and area on the map, it sends a "bounding box" to GeoServer and pulls all of the features with that bounding box. The data is returned in GeoJSON format.

I then need that data to be put nicely on a table. Is it possible (using a for loop) to set up column names only once and then populate the values under it? Right not, my data does this with the Key next to Value:

               **Name:** George Costanza 
               **Occupation: **Bra Salesman/Kruger Industrial Smoothing
              **Strengths:** Lying 
              **Weaknesses:** He was in the pool.

And If I have more than one feature selected it just stacks everything on top of each other and the Key are duplicated again and again:

              **Name:** Person Person 
              **Occupation:** Some Job
              **Strengths:** Some Strength 
              **Weaknesses:** Some Weakness
              **Name:** Other Person 
             **Occupation:** Some OTHER Job
             **Strengths:** Some OTHER Strength 
             **Weaknesses:** Some OTHER weakness
             **Name:** Other Person 2
            **Occupation:** sadfsadds
             **Strengths: **Lasdfafffd
             **Weaknesses:** asdasfdsadf

I need it in this format though...column names only added once and arranged nicely in rows/columns:

Name Occupation Strengths Weaknesses
person1 person1 job person1 strength person1 weakness
person2 person2 job person2 strength person2 weakness
person3 person3 job person3 strength person3 weakness

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260
    edited July 14

    Is your GeoJSON data structured similar to the example here:
    https://datatables.net/manual/data/#Objects

    That page covers the data formats expected by Datatables.

    You may be able to build the Datatable by looping through the first element in your GeoJSON data to get the column names and build an array of objects like this:

    [{data: 'Name', title: 'Name'},
     {data: 'Occupation', title: 'Occupation'},
     {data: 'Strengths', title: 'Strengths'},
     {data: 'Weaknesses', title: 'Weaknesses'}
    ]
    

    The columns.data will be used to match with the JSON data when building the table. The columns.title will build the column title.

    You can then initialize the Datatable with something like this:

        $('#example').DataTable( {
            data: data,   //GeoJSON string
            columns: columns
        } );
    

    Just to make sure my idea works I put together a simple example:
    http://live.datatables.net/fafuyeyu/1/edit

    Please post any questions.

    Kevin

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0
    edited July 17

    Hello Kevin,

    No, my data comes back as a featureCollection like below. I will not know that the column names will be as they will change depending on how many features are selected, and what layer the user has toggled on and off. EVERYTHING will have to be built dynamically. Here is the data:

    getJson({"type":"FeatureCollection",
    "totalFeatures":2,"features":[
    {"type":"Feature","id":"agents.8264","geometry":{"type":"Point","coordinates":[-105.836835,46.408539]},
    "geometry_name":"geom",
    "properties":{
    "st_agent_c":"65986",
    "state_ga_c":"26","agent_code":"1542",
    "name":"George Costanza",
    "pref_name":"CantStandYa",
    "latitude":46.408539,
    "longitude":-105.836835}},
    {"type":"Feature","id":"agents.9612589",
    "geometry":{
    "type":"Point","coordinates [-105.824864,46.401097]},
    geometry_name":"geom",
    "properties":{"st_agent_c":"261352",
    "state_ga_c":"26",
    "agent_code":"969685",
    "name":"Jerry Seinfeld",
    "pref_name":"Fusilli Jerry"
    ,"latitude":46.401097,"longitude":-105.824864}}]

    ,"crs":{"type":"name","properties":{"name":"urn:ogc:def:crs:EPSG::4326"}}})

    I have hacked that code to bits trying to make it a bit more readable, but it does not come back in perfectly structured like the examples. It looks like both type and properties are called "name"... I was able to get both by using the "onEachFeature" method and extracting the type by calling name and Properties by calling feature.properties.name.

    Any suggestions?

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260

    First it doesn't seem like your JSON format is valid. You can copy and paste the JSON string into https://jsonlint.com/ to validate.

    {
        "type": "FeatureCollection",
        "totalFeatures": 2,
        "features": [{
                "type": "Feature",
                "id": "agents.8264",
                "geometry": {
                    "type": "Point",
                    "coordinates": [-105.836835, 46.408539]
                },
                "geometry_name": "geom",
                "properties": {
                    "st_agent_c": "261312",
                    "state_sf_c": "26",
                    "agent_code": "1312",
                    "name": "Fred Wambolt",
                    "pref_name": "Some Guy",
                    "latitude": 46.408539,
                    "longitude": -105.836835
                }
            },
            {
                "type": "Feature",
                "id": "agents.8280",
                "geometry": {
                    "type": "Point",
                    "coordinates [-105.824864,46.401097]},
                    geometry_name ":"
                    geom ",
                    "properties": {
                        "st_agent_c": "261352",
                        "state_sf_c": "26",
                        "agent_code": "1352",
                        "name": "Jerry Seinfeld",
                        "pref_name": "Fusilli Jerry",
                        "latitude": 46.401097,
                        "longitude": -105.824864
                    }
                }]
    
            , "crs": {
                "type": "name",
                "properties": {
                    "name": "urn:ogc:def:crs:EPSG::4326"
                }
            }
        }
    

    The first problem starts at line 27. "coordinates [-105.824864,46.401097]}, should be "coordinates": [-105.824864,46.401097]},. There are other errors after fixing that.

    I fixed a couple issues and this is the resulting JSON string:

    {
        "type": "FeatureCollection",
        "totalFeatures": 2,
        "features": [{
                    "type": "Feature",
                    "id": "agents.8264",
                    "geometry": {
                        "type": "Point",
                        "coordinates": [-105.836835, 46.408539]
                    },
                    "geometry_name": "geom",
                    "properties": {
                        "st_agent_c": "261312",
                        "state_sf_c": "26",
                        "agent_code": "1312",
                        "name": "Fred Wambolt",
                        "pref_name": "Some Guy",
                        "latitude": 46.408539,
                        "longitude": -105.836835
                    }
                },
                {
                    "type": "Feature",
                    "id": "agents.8280",
                    "geometry": {
                        "type": "Point",
                        "coordinates": [-105.824864, 46.401097]
                    },
                    "geometry_name": "geom ",
                    "properties": {
                        "st_agent_c": "261352",
                        "state_sf_c": "26",
                        "agent_code": "1352",
                        "name": "Jerry Seinfeld",
                        "pref_name": "Fusilli Jerry",
                        "latitude": 46.401097,
                        "longitude": -105.824864
                    }
                }
            ]
    
            ,
        "crs": {
            "type": "name",
            "properties": {
                "name": "urn:ogc:def:crs:EPSG::4326"
            }
        }
    }
    

    You can have nested objects. This is an example:
    https://datatables.net/examples/ajax/deep.html

    I built another example using your JSON data to build a table of features.
    http://live.datatables.net/sokitihe/1/edit

    Keep in mind I'm not the most knowledgable Javascript programmer and this is very simplistic code. Others may have a better way to code this. Basically the code loops through the first features object the get the column names. If the column is an object it loops through the object to build the nested columns.

    Another option is to take some of the nested objects (geometry or properties) and use child rows for the detailed information as shown in this example:
    https://datatables.net/examples/api/row_details.html

    Kevin

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

    That looks great! Do you know how I can achieve the same results by pulling the geoJSON data directly from a server (GeoServer) using Ajax? That's how I've got it set up now.

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260
    Answer ✓

    You would something like the first example I provided to get the data:
    http://live.datatables.net/fafuyeyu/1/edit

    Then use code similar to my second example to build the Datatables data structure and columns.

    If you allow the user to perform another query you can use clear() to clear the table before displaying, if the columns are the same. Or you can use destroy() if the column/data structure is different.

    Kevin

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

    Works!! Thank you so much, Kevin. You can't imagine how much this helped me.

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

    Hey, I wanted to say thanks again... aaaaaand would you happen to know how to just start looping through at the "properties" section of the geoJSON? That is actually the only part I need. So in the same format, but starting and ending there? It will be the same with every single layer.

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260

    This example just gets the properties object.
    http://live.datatables.net/sokitihe/3/edit

    Basically it builds the Datatables structure to look like this:

    column: [
    {data: "properties.st_agent_c", title: "st_agent_c"},
    {data: "properties.state_sf_c", title: "state_sf_c"},
    {data: "properties.agent_code", title: "agent_code"},
    {data: "properties.name", title: "name"},
    {data: "properties.pref_name", title: "pref_name"},
    {data: "properties.latitude", title: "latitude"},
    {data: "properties.longitude", title: "longitude"}
    ]
    

    Datatables still parses the data from features with this option:

    data: data.features

    Kevin

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

    Thank you for the update. It looks like that will not work, however. Being that i'm pulling data directly from an Ajax link, there is no way for me to stringify according to the good folks on StackOverflow. I believe if I could somehow start at a later index in the geoJSON object, it may work correct? For instance, when I log the columnNames to the console I get this:

    ["type", "id", "geometry", "geometry_name", "properties"]
    

    I just need the info under the "properties.

    my StackOverflow question and code example:

    https://stackoverflow.com/questions/45166926/cannot-stringify-geojson-data

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260
    edited July 18 Answer ✓

    Sorry, my examples may be confusing. My first example is using a Datatables sample data source via AJAX. It is returning a JSON string which is then parsed into a Javascript object: data = JSON.parse(data);

    My second and third examples are using your sample data. In my code its a Javascript object which I'm converting to a JSON string to emulate what you would get from your server. You don't want to use stringify. Here is my code snippet:

    var GeoJSON = JSON.stringify({
        "type": "FeatureCollection",
        "totalFeatures": 2,
        "features": [{
                    "type": "Feature",
                    "id": "agents.8264",
                    "geometry": {
                        "type": "Point",
                        "coordinates": [-105.836835, 46.408539]
                    },
                    "geometry_name": "geom",
                    "properties": {
                        "st_agent_c": "261312",
                        "state_sf_c": "26",
                        "agent_code": "1312",
                        "name": "Fred Wambolt",
                        "pref_name": "Some Guy",
                        "latitude": 46.408539,
                        "longitude": -105.836835
                    }
                },
    .........
        }
    });
    
    

    My third example extracts only the properties object. I changed the code structure, simplifying it. What you need to do is merge example 1 and example 3 to look something like this:

    function getData(cb_func) {
        $.ajax({
          url: "/ajax/objects.txt",
          success: cb_func
        });
    }
    
    $(document).ready(function() {
      getData(function( data ) {
      var columns = [];
      data = JSON.parse(data);
      var prop = data.features[0].properties;
      for (var i in prop) {
          columns.push({data: 'properties.' + i, title: i});
      }
        $('#example').DataTable( {
            data: data.features,   //get the data under features
            columns: columns
        } );
      });
      
    } );
    

    I think this should set up Datatables to display only the properties object from the features data structure. Essentially you just need to get your GeoJSON data and use JSON.parse and assign it to the variable data then use the code above to build the columns for Datatables. You can do some string manipulation to format the titles if desired.

    Kevin

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

    JSON.parse fails for some reason... :s Console gives this error:

    index.html:1 Uncaught SyntaxError: Unexpected token o in JSON at position 1
    at JSON.parse (<anonymous>)
    at Object.success (index.html:688)
    at i (jquery-3.1.1.min.js:2)
    at Object.fireWith [as resolveWith] (jquery-3.1.1.min.js:2)
    at A (jquery-3.1.1.min.js:4)
    at HTMLScriptElement.c (jquery-3.1.1.min.js:4)
    at HTMLScriptElement.dispatch (jquery-3.1.1.min.js:3)
    at HTMLScriptElement.q.handle (jquery-3.1.1.min.js:3)

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260
    Answer ✓

    I'm not sure what format the data is you are receiving. Maybe its not a JSON string but a Javascript object. If its a JS object then you can eliminate the JSON.parse. If its a JSON string then copy it into https://jsonlint.com/ to validate the string.

    Kevin

  • JeremiahEdwardJeremiahEdward Posts: 25Questions: 4Answers: 0

    Absolutely no need to apologize, your examples go above and beyond what most people would do. I'm happy to report, that will a little tweaking, this works PERFECTLY. You seriously saved my skin. I'm doing an internship building a program in JavaScript... I've never used any of this before (including JS) so I'm this REALLY helps. I'm eternally grateful.

  • kthorngrenkthorngren Posts: 1,293Questions: 16Answers: 260

    Good luck with the internship. You should learn a lot of JS, etc by using Datatables. I know I have.

    Kevin

Sign In or Register to comment.