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

JeremiahEdward

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

kthorngren

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

JeremiahEdward

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?

kthorngren

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

JeremiahEdward

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.

kthorngren

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

JeremiahEdward

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

JeremiahEdward

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.

kthorngren

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

JeremiahEdward

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

kthorngren

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

JeremiahEdward

JSON.parse fails for some reason... 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)

kthorngren

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

JeremiahEdward

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.

kthorngren

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

Kevin

gbiurr

Hi. I know this was closed long ago, but I'm trying to do something similar and can't get it running.
I would appreciate any help, since I'm a newbie on js.
I did a web map which is collecting data from a remote geojson file.
Markers can be filtered via Leaflet Tag Filter Buttons, using tags stored in another json file.
I would like to add a dinamic html table at the bottom collecting only those data already filtered.
I guess it can be done, but I have no clue on how to proceed.

Here are asome pieces of the code I've running now. Full example can be seen here

This is getting all properties to display the markers and add them to the map.

$.getJSON("https://URL_HERE.geojson",function(data){
  var IbSpid = L.geoJson(data,{
    pointToLayer: function(feature,latlng){
      var marker = L.marker(latlng, { tags: feature.properties.Gen.concat(feature.properties.Fam, feature.properties.Spec, feature.properties.Is_unique, feature.properties.Prov, feature.properties.Cou, feature.properties.Date_Range)});
      return marker;
    }
  });
  IbSpid.addTo(mcg);
});

This is getting the tags to add to each filter (shown only one filter).

  $.getJSON('https://Here_URL_for-the-Tags-File.json', function(data) {
var unique = L.control.tagFilterButton({
  data: data.unique,
  filterOnEveryClick: true,
  icon: '<span style="font-size:1.2rem"><b>First records&emsp;</b></span><i class="fas fa-spider" style="font-size:2rem;vertical-align: middle;color:black;"></i>',
}).addTo(map);
unique.enableMCG(mcg);
jQuery('.easy-button-button').click(function() {
    target = jQuery('.easy-button-button').not(this);
    target.parent().find('.tag-filter-tags-container').css({
        'display' : 'none',
    });
});
});

And here the table added, which is collecting all data in the geojson file.
I guess there's something to modify here in order to get those filtered data instead of all of them.

function getData(cb_func) {
    $.ajax({
      url: "https://URL again.geojson",
      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});
  }
    $('#NewARAIB').DataTable( {
        data: data.features,   //get the data under features
        columns: columns
    } );
  });
} );

Thanks a lot in advance.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

Hi @gbiurr ,

Your console is giving this error:

mapCP.js:181 Uncaught TypeError: $(...).DataTable is not a function

So it looks like you're not including the DataTables source files.

Cheers,

Colin

gbiurr

Hi, Colin.
Thanks a lot.
My fault. I'm always working on a local html file while playing around. And it's sometimes tricky to upload it to that free server, since their file manager is not always connecting properly.
That's why that old html did not have all the source files.
Everything is there now.
The table shows fine (I still have to style it, though).
What I was wondering is whether that table could get the data from those previous filters (Tag filter Button).
Besides, it would be great if, instead of just toggle the display of the table, it could be "loaded", since those 25MB of the geojson are kind of duplicated (map and table), and that's a bunch for a simple web page. Although it may be not that user-friendly, since it would require refreshig after every filtering click.
Thanks a lot again.
Regards.
G.

colin

Hi @gbiurr ,

You asked:

What I was wondering is whether that table could get the data from those previous filters (Tag filter Button).

You can specify the source with data - so provided you can get that to DataTables, it can use that data. Also, you can use row.add() and rows.add() to add the data later if that's more convenient.

Cheers,

Colin

gbiurr

Hi @colin ,
As far as I understood source is already specified by the url of the geojson file, and then parsed by

data = JSON. parse(data)

as seen in my previous message, third piece of code.
So, these data provided to the table should be those previosly filtered (second piece of code), (by the way, thanks for the plain text edit).
For each filter (var fam, var gen...) there's a data "creation" called data.fam, data.gen...
I should match those data to the ones I want in my table, some sort of concatenate the filtered properties... And I've got no clue on how to do it.
I guess right now my js skills are pretty clear

Thanks again.
Regards.
G.