DataTables and Web Feature Services (WFS)

DataTables and Web Feature Services (WFS)

RockERockE Posts: 31Questions: 3Answers: 0

Hi There,

I'm wondering whether DataTables can handle a WFS link at all to populate a table?

I'm looking at trying to modify this https://datatables.net/forums/discussion/62822 to work with a WFS.

Specifically https://ecat.ga.gov.au/geonetwork/srv/eng/catalog.search#/metadata/144577 and the bh:Boreholes layer within. The properties listed below should be correct based off the data dictionary PDF on this page.

<link rel="stylesheet" type="text/css"String href="https://cdn.datatables.net/v/dt/jq-3.3.1/jszip-2.5.0/dt-1.10.20/b-1.6.1/b-html5-1.6.1/r-2.2.3/datatables.min.css"/>

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.3.1/jszip-2.5.0/dt-1.10.20/b-1.6.1/b-html5-1.6.1/r-2.2.3/datatables.min.js"></script>

    <script type="text/javascript" class="init">
$(document).ready(function() {
$('#example').DataTable( {
    "ajax":{
        "url":"'link here'",
        "dataSrc": "features"
    },
    "columns": [
      { "data": "properties.IDENTIFIER"},
      { "data": "properties.NAME" },
      { "data": "properties.STATUS" }
    ]
  });
});
    </script>

</HEAD>

<table id="example" class="display wrap" cellspacing="0" width="100%">
    <thead>
      <tr>
        <th>IDENTIFIER</th>
        <th>NAME</th>
        <th>STATUS</th>
      </tr>
    </thead>
    <tfoot>
        <tr>
        <th>IDENTIFIER</th>
        <th>NAME</th>
        <th>STATUS</th>
        </tr>
        </tfoot>
</table>

Cheers

RockE

Replies

  • kthorngrenkthorngren Posts: 16,746Questions: 25Answers: 3,966

    I took a quick look at the link but did not see where is discussed the format of the returned data. The Ajax docs state the Datatables supports JSON formatted data. Do you know if the WFS link supports returning JSON data?

    In addition the row data is expected to be in an array. So either an array of objects or an array with each row being an array. More info in the Data source types doc.

    If the data returned is not JSON then you can use the ajax.dataSrc option as a function to manipulate the data into a Datatables supported format.

    Kevin

  • RockERockE Posts: 31Questions: 3Answers: 0

    Hi Kevin,

    This "Do you know if the WFS link supports returning JSON data?" got me thinking.

    So it seems WFS version 2.0 supports JSON.

    https://gis.stackexchange.com/questions/251734/how-to-get-wfs-getcapabilities-information-as-json
    
    https://community.esri.com/t5/arcgis-api-for-javascript-questions/wfs-in-json/m-p/687265#M63972
    

    I tried this but it didn't work, more digging needed and to see if that link I posted supports version 2.

    https://services.ga.gov.au/gis/boreholes/wfs?service=wfs&version=2.0.0request=GetFeature&typeNames=layer-bh.boreholes&outputFormat=application/json
    

    Cheers

    RockE

  • RockERockE Posts: 31Questions: 3Answers: 0

    A WFS Reference

    https://docs.geoserver.org/stable/en/user/services/wfs/reference.html
    
  • RockERockE Posts: 31Questions: 3Answers: 0

    Hi Kevin,

    The WFS version is 2 and the capabilities states the output can be json. 2 scrolls down on the mouse and you'll see the reference to outputFormat. paste link into a browser

    https://services.ga.gov.au/gis/boreholes/wfs?&service=wfs&version=2.0.0&request=getCapabilities

    I'm not sure if the data output shown in the browser is valid json for this link.

    https://services.ga.gov.au/gis/boreholes/wfs?&service=wfs&version=2.0.0&request=getFeature&typeNames=bh:Boreholes&maxFeatures=50&outputFormat=application/json

    And I appear to have doubled up on this question, can you delete the other pls.

    https://datatables.net/forums/discussion/73209

    Cheers

    RockE

  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin

    Hi,

    That JSON file is 108MB! Yes it looks like something DataTables could use, but that large a file is going to cause problems for the browser - I wouldn't be surprised if the browser were to crash loading such a file (Firefox for me certainly had issues displaying it).

    Allan

  • RockERockE Posts: 31Questions: 3Answers: 0

    Hi Alan,

    108MB ouch. thanks for looking at it.

    Back to the drawing board.

    Cheers

    RockE

  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin

    One option would be to proxy it in your own database and then use DataTables with server-side processing against it. Have a cron job or similar that will repopulate your own database from the original source once a week, or however often you think would be needed to keep it up to date (without abusing and getting banned from the source site!).

    Allan

  • RockERockE Posts: 31Questions: 3Answers: 0

    Thanks Allan good idea :-)

  • RockERockE Posts: 31Questions: 3Answers: 0
    edited June 30

    Hi Allan,

    I saved a subset of that json file at 101kb but still can't get DT to display anything as it's just stuck on loading. Could you advise on what I'm doing wrong here please.

    $(document).ready(function() {
    $('#example').DataTable( {
        "ajax":{
            "url":'test.json',
            "dataSrc": "features"
        },
        "columns": [
          { "data": "properties.IDENTIFIER" },
          { "data": "properties.NAME" },
          { "data": "properties.STATUS" },
        ],
            dom: 'lfrtBip',
            buttons: [
                'copy', 'excel', 'pdf', 'csv'
            ]
      });
    });
    
    <table id="example" class="display wrap" cellspacing="0" width="100%">
        <thead>
          <tr>
            <th>IDENTIFIER</th>
            <th>NAME</th>
            <th>STATUS</th>
          </tr>
        </thead>
        <tfoot>
            <tr>
            <th>IDENTIFIER</th>
            <th>NAME</th>
            <th>STATUS</th>
            </tr>
            </tfoot>
    </table>
    

    Here's a single entry...

    {
        "type": "FeatureCollection",
        "features": [{
                    "type": "Feature",
                    "id": "Boreholes.651478",
                    "geometry": {
                        "type": "Point",
                        "coordinates": [148.019306, -33.345153]
                    },
                    "geometry_name": "SHAPE",
                    "properties": {
                        "IDENTIFIER": "http://pid.geoscience.gov.au/samplingFeature/au/BH651478",
                        "NAME": "CALDD003",
                        "PURPOSE": "minerals - exploration",
                        "STATUS": "unknown",
                        "DRILLINGMETHOD": null,
                        "OPERATOR": "Hargraves Resources NL, Tri Origin Australia NL",
                        "DRILLER": null,
                        "DRILLSTARTDATE": null,
                        "DRILLENDDATE": null,
                        "STARTPOINT": "onshore",
                        "INCLINATIONTYPE": null,
                        "BOREHOLEMATERIALCUSTODIAN": null,
                        "BOREHOLELENGTH_M": 351,
                        "ELEVATION_M": 244,
                        "ELEVATION_SRS": "http://www.opengis.net/def/crs/EPSG/0/5711",
                        "POSITIONALACCURACY": "100 metres",
                        "SOURCE": "Geoscience Australia Borehole Database",
                        "PARENTBOREHOLE_URI": null,
                        "METADATA_URI": "http://pid.geoscience.gov.au/dataset/ga/126310",
                        "ENO": 651478,
                        "GMLID": "gsmlp.boreholeview.651478",
                        "REPORTED_STATUS_DATE": null,
                        "STATUS_DATE_QUALIFIER": "date unknown",
                        "NVCLCOLLECTION": null,
                        "NVCLSCANDATE": null,
                        "NVCLINSTRUMENT": null,
                        "NVCLSOFTWAREVER": null,
                        "NVCLDATAPROCDATE": null,
                        "PROJECT": null,
                        "TENEMENT": null,
                        "COLLAR_LONG_GDA94": 148.019306,
                        "COLLAR_LAT_GDA94": -33.345153,
                        "COLLAR_EASTING_MGA94": 594849.41,
                        "COLLAR_NORTHING_MGA94": 6309985.94,
                        "UTM_ZONE": 55,
                        "COLLAR_AZIMUTH": null,
                        "COLLAR_INCLINATION": null,
                        "OBSERVATION_METHOD": "State or NT agency supplied location, method unknown",
                        "OBSERVATION_DATE": "1998-08-06",
                        "LOCATION_DESC": "Nil Desperandum Mine - Calarie",
                        "COUNTRY": "AUS",
                        "STATE": "NSW",
                        "GEOLOGICAL_PROVINCES": "Lachlan Orogen",
                        "ALTERNATE_NAMES": null,
                        "ASSOCIATED_BOREHOLE": null,
                        "LEGISLATION": null,
                        "RIG_NAME": null,
                        "RIG_RELEASE_DATE": null,
                        "KICKOFF_DEPTH": null,
                        "DEPTH_REF_POINTS": "ground surface, 244 m above Australian Height Datum",
                        "BOREHOLE_REMARK": null,
                        "LINKED_FILES": "N",
                        "EARTH_MATERIALS_OBSERVATIONS": 1,
                        "STRATIGRAPHY_OBSERVATIONS": 1,
                        "STRUCTURES_OBSERVATIONS": 0,
                        "INORGANIC_GEOCHEM_ANALYSES": 0,
                        "ORGANIC_GEOCHEM_ANALYSES": 0,
                        "GEOCHRONOLOGY_ANALYSES": 0,
                        "HYDROCHEMISTRY_ANALYSES": 0,
                        "ROCK_PROPERTY_MEASUREMENTS": 0,
                        "STORED_SPECIMENS": 1,
                        "THIN_SECTIONS": 0,
                        "ISOTOPE_GROUPS": 0,
                        "OTHER_SAMPLE_DATA": 0,
                        "QA_STATUS": "checked",
                        "BOREHOLE_REPORT_URI": "https://portal.ga.gov.au/bhcr/minerals/651478"
                    }
                }
          ]
    }
    
  • RockERockE Posts: 31Questions: 3Answers: 0

    Hi Alan,

    I saved a subset of the data into a local file. However I still can't get DataTables to display anything as it hangs at loading. Can you advise on what I'm doing wrong here please.

    <script type="text/javascript" class="init">
    $(document).ready(function() {
    $('#example').DataTable( {
        "ajax":{
            "url":"test.json",
            "dataSrc": "features"
        },
        "columns": [
          { "data": "properties.IDENTIFIER"},
          { "data": "properties.NAME" },
          { "data": "properties.STATUS" },
        ]
      });
    });
        </script>
    
    </HEAD>
    
    <table id="example" class="display wrap" cellspacing="0" width="100%">
        <thead>
          <tr>
            <th>IDENTIFIER</th>
            <th>NAME</th>
            <th>STATUS</th>
          </tr>
        </thead>
        <tfoot>
            <tr>
            <th>IDENTIFIER</th>
            <th>NAME</th>
            <th>STATUS</th>
            </tr>
            </tfoot>
    </table>
    

    Cheers

  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin

    Can you pastebin or something your reduced JSON file so I can take a look at it please?

    Thanks,
    Allan

  • RockERockE Posts: 31Questions: 3Answers: 0

    Here you go a single entry to save into a file.

    {
        "type": "FeatureCollection",
        "features": [{
                    "type": "Feature",
                    "id": "Boreholes.651478",
                    "geometry": {
                        "type": "Point",
                        "coordinates": [148.019306, -33.345153]
                    },
                    "geometry_name": "SHAPE",
                    "properties": {
                        "IDENTIFIER": "http://pid.geoscience.gov.au/samplingFeature/au/BH651478",
                        "NAME": "CALDD003",
                        "PURPOSE": "minerals - exploration",
                        "STATUS": "unknown",
                        "DRILLINGMETHOD": null,
                        "OPERATOR": "Hargraves Resources NL, Tri Origin Australia NL",
                        "DRILLER": null,
                        "DRILLSTARTDATE": null,
                        "DRILLENDDATE": null,
                        "STARTPOINT": "onshore",
                        "INCLINATIONTYPE": null,
                        "BOREHOLEMATERIALCUSTODIAN": null,
                        "BOREHOLELENGTH_M": 351,
                        "ELEVATION_M": 244,
                        "ELEVATION_SRS": "http://www.opengis.net/def/crs/EPSG/0/5711",
                        "POSITIONALACCURACY": "100 metres",
                        "SOURCE": "Geoscience Australia Borehole Database",
                        "PARENTBOREHOLE_URI": null,
                        "METADATA_URI": "http://pid.geoscience.gov.au/dataset/ga/126310",
                        "ENO": 651478,
                        "GMLID": "gsmlp.boreholeview.651478",
                        "REPORTED_STATUS_DATE": null,
                        "STATUS_DATE_QUALIFIER": "date unknown",
                        "NVCLCOLLECTION": null,
                        "NVCLSCANDATE": null,
                        "NVCLINSTRUMENT": null,
                        "NVCLSOFTWAREVER": null,
                        "NVCLDATAPROCDATE": null,
                        "PROJECT": null,
                        "TENEMENT": null,
                        "COLLAR_LONG_GDA94": 148.019306,
                        "COLLAR_LAT_GDA94": -33.345153,
                        "COLLAR_EASTING_MGA94": 594849.41,
                        "COLLAR_NORTHING_MGA94": 6309985.94,
                        "UTM_ZONE": 55,
                        "COLLAR_AZIMUTH": null,
                        "COLLAR_INCLINATION": null,
                        "OBSERVATION_METHOD": "State or NT agency supplied location, method unknown",
                        "OBSERVATION_DATE": "1998-08-06",
                        "LOCATION_DESC": "Nil Desperandum Mine - Calarie",
                        "COUNTRY": "AUS",
                        "STATE": "NSW",
                        "GEOLOGICAL_PROVINCES": "Lachlan Orogen",
                        "ALTERNATE_NAMES": null,
                        "ASSOCIATED_BOREHOLE": null,
                        "LEGISLATION": null,
                        "RIG_NAME": null,
                        "RIG_RELEASE_DATE": null,
                        "KICKOFF_DEPTH": null,
                        "DEPTH_REF_POINTS": "ground surface, 244 m above Australian Height Datum",
                        "BOREHOLE_REMARK": null,
                        "LINKED_FILES": "N",
                        "EARTH_MATERIALS_OBSERVATIONS": 1,
                        "STRATIGRAPHY_OBSERVATIONS": 1,
                        "STRUCTURES_OBSERVATIONS": 0,
                        "INORGANIC_GEOCHEM_ANALYSES": 0,
                        "ORGANIC_GEOCHEM_ANALYSES": 0,
                        "GEOCHRONOLOGY_ANALYSES": 0,
                        "HYDROCHEMISTRY_ANALYSES": 0,
                        "ROCK_PROPERTY_MEASUREMENTS": 0,
                        "STORED_SPECIMENS": 1,
                        "THIN_SECTIONS": 0,
                        "ISOTOPE_GROUPS": 0,
                        "OTHER_SAMPLE_DATA": 0,
                        "QA_STATUS": "checked",
                        "BOREHOLE_REPORT_URI": "https://portal.ga.gov.au/bhcr/minerals/651478"
                    }
                }, {
                    "type": "Feature",
                    "id": "Boreholes.6322",
                    "geometry": {
                        "type": "Point",
                        "coordinates": [149.127614, -35.286861]
                    },
                    "geometry_name": "SHAPE",
                    "properties": {
                        "IDENTIFIER": "http://pid.geoscience.gov.au/samplingFeature/au/BH6322",
                        "NAME": "BMR Canberra 253",
                        "PURPOSE": "stratigraphic - stratigraphic investigation",
                        "STATUS": "completed",
                        "DRILLINGMETHOD": null,
                        "OPERATOR": "Bureau of Mineral Resources",
                        "DRILLER": "Bureau of Mineral Resources",
                        "DRILLSTARTDATE": "1978-07-21",
                        "DRILLENDDATE": "1978-07-21",
                        "STARTPOINT": "onshore",
                        "INCLINATIONTYPE": null,
                        "BOREHOLEMATERIALCUSTODIAN": null,
                        "BOREHOLELENGTH_M": 11.5,
                        "ELEVATION_M": 571.4,
                        "ELEVATION_SRS": "http://www.opengis.net/def/crs/EPSG/0/5711",
                        "POSITIONALACCURACY": "100 metres",
                        "SOURCE": "Geoscience Australia Borehole Database",
                        "PARENTBOREHOLE_URI": null,
                        "METADATA_URI": "http://pid.geoscience.gov.au/dataset/ga/126310",
                        "ENO": 6322,
                        "GMLID": "gsmlp.boreholeview.6322",
                        "REPORTED_STATUS_DATE": "1978-07-21",
                        "STATUS_DATE_QUALIFIER": "estimated",
                        "NVCLCOLLECTION": "false",
                        "NVCLSCANDATE": null,
                        "NVCLINSTRUMENT": null,
                        "NVCLSOFTWAREVER": null,
                        "NVCLDATAPROCDATE": null,
                        "PROJECT": "Gould st - Commonwealth Ave Sewer project",
                        "TENEMENT": null,
                        "COLLAR_LONG_GDA94": 149.127614,
                        "COLLAR_LAT_GDA94": -35.286861,
                        "COLLAR_EASTING_MGA94": 693483.75,
                        "COLLAR_NORTHING_MGA94": 6093070.02,
                        "UTM_ZONE": 55,
                        "COLLAR_AZIMUTH": null,
                        "COLLAR_INCLINATION": null,
                        "OBSERVATION_METHOD": "map",
                        "OBSERVATION_DATE": "1978-07-21",
                        "LOCATION_DESC": "Western side of Commonwealth Ave",
                        "COUNTRY": "AUS",
                        "STATE": "ACT",
                        "GEOLOGICAL_PROVINCES": "Lachlan Orogen",
                        "ALTERNATE_NAMES": "C253",
                        "ASSOCIATED_BOREHOLE": null,
                        "LEGISLATION": null,
                        "RIG_NAME": null,
                        "RIG_RELEASE_DATE": null,
                        "KICKOFF_DEPTH": null,
                        "DEPTH_REF_POINTS": "ground surface",
                        "BOREHOLE_REMARK": null,
                        "LINKED_FILES": "Y",
                        "EARTH_MATERIALS_OBSERVATIONS": 0,
                        "STRATIGRAPHY_OBSERVATIONS": 0,
                        "STRUCTURES_OBSERVATIONS": 0,
                        "INORGANIC_GEOCHEM_ANALYSES": 0,
                        "ORGANIC_GEOCHEM_ANALYSES": 0,
                        "GEOCHRONOLOGY_ANALYSES": 0,
                        "HYDROCHEMISTRY_ANALYSES": 0,
                        "ROCK_PROPERTY_MEASUREMENTS": 0,
                        "STORED_SPECIMENS": 0,
                        "THIN_SECTIONS": 0,
                        "ISOTOPE_GROUPS": 0,
                        "OTHER_SAMPLE_DATA": 0,
                        "QA_STATUS": "checked",
                        "BOREHOLE_REPORT_URI": "https://portal.ga.gov.au/bhcr/minerals/6322"
                    }
                }
          ]
    }
    
  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin

    That looks like it should work to me. Here is a little example of it: http://live.datatables.net/buyinidu/1/edit . I've used local data rather than Ajax loading, but your Ajax config looks fine there.

    Perhaps you can give me a link to a page showing the error please?

    Allan

  • RockERockE Posts: 31Questions: 3Answers: 0

    Hi Allen,

    You can use the example on the bottom of this page it works with the listed url. https://datatables.net/forums/discussion/67714

    I've saved that url into a file and then tried it and it wouldn't work (stuck on loading). I must be missing something in the code when using a local file over a url?

    Here's the table so you can copy and paste.

    <table id="example" class="display" cellspacing="0" width="100%">
    <thead>
      <tr>
        <th>WELL_NAME</th>
        <th>UWI</th>
        <th>BASIN</th>
        <th>OPERATOR</th>
        <th>STATUS</th>
        <th>SPUD_DATE</th>
        <th>RIG_RELEASE</th>
      </tr>
    </thead>
    <tfoot>
        <tr>
         <th>WELL_NAME</th>
         <th>UWI</th>
         <th>BASIN</th>
         <th>OPERATOR</th>
         <th>STATUS</th>
         <th>SPUD_DATE</th>
         <th>RIG_RELEASE</th>
        </tr>
        </tfoot>
    </table>
    

    Cheers

    RockE

  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin

    Is there anything shown on your browser’s console?

    Also I’m not quite clear which example to the thread you are referring to - the one from Kevin?

    Allan

  • RockERockE Posts: 31Questions: 3Answers: 0

    Hi Allan,

    The code at the end where I have this comment. - "Awesome thanks Kevin it works a treat. Putting the final solution here for others to see."

    I think I found a reason for it not working, I saw a post of yours stating ajax won't load a local file. I'll upload the json file to a hosting solution to see if then works. If not then back to the drawing board.

    Cheers

    RockE

  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin

    Thanks for the clarification. Yes, Ajax doesn't work with file:// loaded pages due to the security risk of that.

    Allan

Sign In or Register to comment.