Java, XML and Postgres

I'm migrating from Oracle 11g to Postgres for an ongoing project. Since I found few really helpful code examples online about how to load XML into Postgres using Java, I am posting my method here. It's a first run at it that worked. Enhancements will come later so please keep the hating to a minimum, there's always other ways of coding.

package edu.harvard.hul.lts;

import java.sql.*;
import java.io.*;
import java.lang.*;
import java.util.*;

public class Main {

    private static String driver = "";
    private static String instance = "";
    private static String user = "";
    private static String pass = "";

    public static void main(String[] args) {

        // Read the properties file         

        try {
            getProps();
        } catch (IOException e) {
            e.printStackTrace();
        }

        boolean proc_gsNew = false;

        // Get an array of all TABLE_NAMEs and get/set the Max ID. 

        int maxID = 0;
        List<gsNew> list = new ArrayList<gsNew>();

        if (proc_gsNew) {

            try {
                maxID = getMaxID("GS_NEW");
            } catch (IOException e) {
                e.printStackTrace();
            }

            if (maxID == 0) {
                System.out.println("[ERROR ASSIGNING MAX ID] Exiting...");
                System.exit(0);
            }

            try {
                list = getRecords(list, 9700, 11);
            } catch (IOException e) {
                e.printStackTrace();
            }

            for (gsNew o: list) {
                try {
                    o.MET_XML = getFGDCMetadataAsUTF8String(

                        o.TABLE_NAME);
                } catch (Exception xx) {
                    System.out.println("[MAJOR ERROR OCCURRED]\n");
                    xx.printStackTrace();
                }
            }

            // Next, add 1 record/object at a time to Postgres             

            for (gsNew o: list) {

                try {
                    int result = insertXmlPgObj( maxID, o );
                    maxID++;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            list = null;
            maxID = 0;
        }

        System.out.println("End....");

        /*                Postgres calls         */ 

         String url = "jdbc:postgresql://localhost/OGP";
        String user = "postgres";
        String password = "postgres";

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM \"GS_NEW\"");

            while (rs.next()) {
                System.out.println(rs.getString(2)+"\n"+rs.getString(3));
            }

        } catch (SQLException ex) {
            System.out.println("SEVERE");
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                System.out.println("SEVERE");
                ex.printStackTrace();
            }
        }
    }

    private static void getProps() throws IOException {
        InputStream inputStream = null;
        Properties prop = new Properties();

        try {
            String propFileName = "project.properties";
            inputStream = Main.class.getClassLoader().getResourceAsStream(propFileName);

            if (inputStream != null) {
                prop.load(inputStream);
            } else {
                throw new FileNotFoundException("property file '" + propFileName + "' not found in the classpath");
            }

            // get the property value and print it out             

            instance = prop.getProperty("db.instance");
            driver = prop.getProperty("db.driver");
            user = prop.getProperty("db.user");
            pass = prop.getProperty("db.pass");

            inputStream.close();

        } catch (Exception e) {
            System.out.println("Exception: " + e);
        }
    }

    /*     * Call: Add data to the GS_NEW Postgres table 

     * @param int ID Start ID of record (primary key) 

     * @param gsNew o; List of gsNew objects that were already populated 

     * @return int: Status code of success 

     */     

    public static int insertXmlPgObj( int ID, gsNew o ) throws IOException {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;


        String url = "jdbc:postgresql://localhost/OGP?stringtype=unspecified";  

        String user = "postgres";
        String password = "postgres";

        String theDTD = "<!DOCTYPE metadata SYSTEM \"http://hgl.harvard.edu:8080/HGL/html/fgdc-std-001-1998.dtd\">";
        String theFGDCDTD = "<!DOCTYPE metadata SYSTEM \"http://www.fgdc.gov/metadata/fgdc-std-001-1998.dtd\">";
        //String theXMLDtd = "\uFEFF<?xml version=\"1.0\" encoding=\"UTF-8\"?>"; 

        String theXMLDtd = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";

        o.MET_XML = o.MET_XML.replace(theFGDCDTD, "");
        o.MET_XML = o.MET_XML.replace(theDTD, "");
        o.MET_XML = o.MET_XML.replace(theXMLDtd, "");

        String query;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        int result = 1;

        try {
            con = DriverManager.getConnection(url, user, password);

            query = "INSERT INTO \"GS_NEW\"" +
                    "(\"ID\", \"TABLE_NAME\", \"FGDC\", \"DESCRIPTION\", \"PUBID\", " +
                    "\"GEOMETRY_TYPE\", \"PUB_XML\", \"PUB_245A\", \"PUB_520A\", \"HGL_LAYER_ID\"," +
                    "\"ACCESS_FLAG\"," +
                    "\"MINX\", \"MINY\", \"MAXX\", \"MAXY\", \"DATA_TYPE\") " +
                    "VALUES (?, ?, xml(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            pstmt = con.prepareStatement(query);  

          pstmt.setInt(1, o.ID);

          pstmt.setString(2, o.TABLE_NAME);  

          pstmt.setString(3, o.MET_XML);
            pstmt.setString(4, o.DESCRIPTION);
            pstmt.setInt(5, o.PUBID);
            pstmt.setInt(6, o.GEOMETRY_TYPE);
            pstmt.setString(7, o.PUB_XML);
            pstmt.setString(8, o.PUB_245A);
            pstmt.setString(9, o.PUB_520A);
            pstmt.setInt(10, o.HGL_LAYER_ID);
            pstmt.setString(11, o.ACCESS_FLAG);
            pstmt.setFloat(12, o.MINX);
            pstmt.setFloat(13, o.MINY);
            pstmt.setFloat(14, o.MAXX);
            pstmt.setFloat(15, o.MAXY);
            pstmt.setString(16, o.DATA_TYPE);

            if (pstmt.executeUpdate() == 1) {
                result = 1;
                System.out.println (ID+" Successfully updated the XML record.");
            } else {
                result = 2;
            }

            con.close();

        } catch (SQLException e) {
            System.out.println ("Problems updating the XML record for table: "+o.TABLE_NAME+". The record probably does not exist in gs_new.");
            e.printStackTrace();
            return result;
        }

        return result;
    } 

    public static int insertXML(String fileNameAndPath, String tableName, int ID) throws IOException {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;


        String url = "jdbc:postgresql://localhost/OGP?stringtype=unspecified"; 

        String user = "postgres";
        String password = "postgres";

        String theDTD = "<!DOCTYPE metadata SYSTEM \"http://hgl.harvard.edu:8080/HGL/html/fgdc-std-001-1998.dtd\">";
        String theFGDCDTD = "<!DOCTYPE metadata SYSTEM \"http://www.fgdc.gov/metadata/fgdc-std-001-1998.dtd\">";
        //String theXMLDtd = "\uFEFF<?xml version=\"1.0\" encoding=\"UTF-8\"?>"; 

        String theXMLDtd = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";

        int status = 0;

        // Change fileNameAndPath to the tempfile 

        String tempfileName = "tempXMLFile";

        // First read the data as UTF8 

        String xmlData = readUTF8InputFile(fileNameAndPath);

        // Create temp file 

        File tempFile = null;
        tempFile = File.createTempFile(tempfileName, ".tmp" );
        tempfileName = tempFile.getAbsolutePath();

        // Remove the tempfile if present in project 

        boolean isDeleted = tempFile.delete();

        // Remove the <!DOCTPE> 

        xmlData = xmlData.replace(theFGDCDTD, "");
        xmlData = xmlData.replace(theDTD, "");
        xmlData = xmlData.replace(theXMLDtd, "");

        // Write xmlData to the tempfile as UTF8 { For the purpose of checking data after loading } 

      Writer out = new BufferedWriter(new OutputStreamWriter(
                new FileOutputStream(tempFile), "UTF8"));
        out.append(xmlData);
        out.flush();
        out.close();

        //System.out.print(xmlData);
        String query;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        int result = 1;

        try {
            con = DriverManager.getConnection(url, user, password);

            // MODIFIED FOR TESTING 

            query = "INSERT INTO \"GS_NEW\"(\"ID\", \"TABLE_NAME\", \"FGDC\") VALUES (?, ?, xml(?))";

            pstmt = con.prepareStatement(query); // create a statement 

            pstmt.setInt(1, ID); // set input parameter 1 

            pstmt.setString(2, tableName); // set input parameter 2 

            pstmt.setString(3, xmlData);

            if (pstmt.executeUpdate() == 1) {
                result = 1;
                System.out.println ("Successfully updated the XML record.");
            } else {
                result = 2;
            }

        } catch (SQLException e) {
            System.out.println ("Problems updating the XML record. The record probably does not exist in gs_new.");
            e.printStackTrace();
            return result;
        }

        return result;
    }

    /* 

     * Call: String fileData = readUTF8InputFile("/Users/Documents/Files/meta/NHGIS_POP1810.xml"); 

     * @param fileName String The name of the xml file to read 

     * @return String A String holding the contents of the input file 

    */ 

    private static String readUTF8InputFile(String fileName) {

        System.out.println("Entering hu.readUTF8InputFile()....");
        String UTF8Str = "";
        String theDTD = "<!DOCTYPE metadata SYSTEM \"http://hgl.harvard.edu:8080/HGL/html/fgdc-std-001-1998.dtd\">";
        String theFGDCDTD = "<!DOCTYPE metadata SYSTEM \"http://www.fgdc.gov/metadata/fgdc-std-001-1998.dtd\">";

        try {

            File fileDir = new File(fileName);
            BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(fileDir), "UTF8"));

            String str;

            while ((str = in.readLine()) != null) {
                UTF8Str += str;
            }

            in.close();

            // If the result string has the typical DOCTYPE then remove it (theDTD or theFGDCDTD) because 

            if (UTF8Str.indexOf(theDTD) > 0)
                UTF8Str = UTF8Str.replace(theDTD, "");
            if (UTF8Str.indexOf(theFGDCDTD) > 0)
                UTF8Str = UTF8Str.replace(theFGDCDTD, "");

        }  catch (UnsupportedEncodingException e) {
            System.out.println(e.getMessage());
        } catch (IOException e) {
            System.out.println(e.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        System.out.println("Exiting hu.readUTF8InputFile()....");

        return UTF8Str;
    }

    /** Get FGDC metadata for a downloadable HGL resource. 

     * @param LayerName - HGL_TABLE_NAME from the ArcSDE LAYERS table 

     * @return String The metadata XML 

     */ 

    public static String getFGDCMetadataAsUTF8String(String LayerName) throws IOException {

        String theDTD = "<!DOCTYPE metadata SYSTEM \"http://hgl.harvard.edu:8080/HGL/html/fgdc-std-001-1998.dtd\"><metadata>";
        String fgdcRecord = "";
        String resultLine = "UNSET";

        java.sql.Connection _con = null;

        //String theQuery = "select e.met_xml.getClobval() as myxml from gs_new e WHERE TABLE_NAME = '"+LayerName.trim()+"'";         

        String theQuery = "select e.met_xml.getBlobVal(1) as myxml from gs_new e WHERE TABLE_NAME = '"+LayerName.trim()+"'";

        try {

            Class.forName(driver).newInstance();
            _con = java.sql.DriverManager.getConnection(instance, user, pass);

            Statement stmnt = _con.createStatement();
            ResultSet rs = stmnt.executeQuery(theQuery);

            if (rs.next()) {
                fgdcRecord = getBlobAsString(rs.getBlob("myxml"));
            }

            rs.close();
            stmnt.close();
            _con.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        fgdcRecord = fgdcRecord.replaceFirst("<metadata>", theDTD);
        return fgdcRecord;

    }

    private static String getBlobAsString(Blob blob) {
        StringBuffer result = new StringBuffer();

        if ( blob != null ) {
            int read = 0;
            Reader reader = null;
            char[] buffer = new char[1024];

            try {
                reader = new InputStreamReader(blob.getBinaryStream(), "UTF8");

                while((read = reader.read(buffer)) != -1) {
                    result.append(buffer, 0, read);
                }
            } catch(SQLException ex) {
                throw new RuntimeException("Unable to read blob data.", ex);
            } catch(IOException ex) {
                throw new RuntimeException("Unable to read blob data.", ex);
            } finally {
                try { if(reader != null) reader.close(); } catch(Exception ex) {};
            }
        }

        return result.toString();
    }

    /** Get a table_name of a particular layer 

     * @param int layerID Layer ID to look at 

     * @return String A table_name string 

     */ 

     public static String getTablename(int layerID) throws IOException {

        String layerName = "";

        java.sql.ResultSet _resultSet = null;
        java.sql.Connection _con = null;

        String theQuery = "SELECT TABLE_NAME FROM GS_NEW WHERE HGL_LAYER_ID = " + layerID;

        try {
            Class.forName(driver).newInstance();
            _con = java.sql.DriverManager.getConnection(instance, user, pass);

            Statement stmnt = _con.createStatement();
            ResultSet rs = stmnt.executeQuery(theQuery);

            if (rs.next()){
                layerName = rs.getString("TABLE_NAME");
            }

            rs.close();
            stmnt.close();
            _con.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return layerName;
    }

    /** 

     * @return Unordered <List> of table names starting from 'start' location 

     * @param int start: Start position of ID in table 

    */ 

    public static List getTableNamesAsList(int start, int limit) throws IOException {

        List<String> list = new ArrayList<String>();

        java.sql.ResultSet _resultSet = null;
        java.sql.Connection _con = null;

        String theQuery = "SELECT TABLE_NAME FROM GS_NEW WHERE ID > "+start+" AND ROWNUM <= "+ limit +" ORDER BY TABLE_NAME";

        try {
            Class.forName(driver).newInstance();
            _con = java.sql.DriverManager.getConnection(instance, user, pass);

            Statement stmnt = _con.createStatement();
            ResultSet rs = stmnt.executeQuery(theQuery);

            while (rs.next()) {
                list.add( rs.getString("TABLE_NAME"));
            }

            rs.close();
            stmnt.close();
            _con.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /** 

     * @return Unordered <List> of table data starting from 'start' location 

     * @param list A gsNew object that you want populated with data 

     * @param int start: Start position of ID in table 

     * @param int limit: The number of records that you want returned 

     */ 

    public static List getRecords(List<gsNew> list, int start, int limit) throws IOException {

        java.sql.ResultSet _resultSet = null;
        java.sql.Connection _con = null;

        String theQuery = "SELECT * FROM GS_NEW WHERE ID > "+start+" AND ROWNUM <= "+ limit +" ORDER BY TABLE_NAME";

        try {
            Class.forName(driver).newInstance();
            _con = java.sql.DriverManager.getConnection(instance, user, pass);

            Statement stmnt = _con.createStatement();
            ResultSet rs = stmnt.executeQuery(theQuery);

            while (rs.next()) {

                gsNew obj = new gsNew();

                obj.TABLE_NAME = rs.getString("TABLE_NAME");
                obj.DESCRIPTION = rs.getString("DESCRIPTION");
                obj.PUB_245A = rs.getString("PUB_245A");
                obj.PUB_520A = rs.getString("PUB_520A");
                obj.DATA_TYPE = rs.getString("DATA_TYPE");
                obj.ACCESS_FLAG = rs.getString("ACCESS_FLAG");
                obj.PUB_XML = rs.getString("PUB_XML");

                obj.ID = rs.getInt("ID");
                obj.PUBID = rs.getInt("PUBID");
                obj.GEOMETRY_TYPE = rs.getInt("GEOMETRY_TYPE");
                obj.HGL_LAYER_ID = rs.getInt("HGL_LAYER_ID");

                obj.MINX = rs.getFloat("MINX");
                obj.MINY = rs.getFloat("MINY");
                obj.MAXX = rs.getFloat("MAXX");
                obj.MAXY = rs.getFloat("MAXY");

                list.add( obj );

            }

            rs.close();
            stmnt.close();
            _con.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }


    /** Get the max ID from table passed 

    * @return int MaxID of records 

    */

    public static int getMaxID(String tableName) throws IOException {

        int maxID = 0;
        java.sql.ResultSet _resultSet = null;
        java.sql.Connection _con = null;

        String theQuery = "SELECT MAX(ID) AS MAX FROM "+tableName;

        try {
            Class.forName(driver).newInstance();
            _con = java.sql.DriverManager.getConnection(instance, user, pass);

            Statement stmnt = _con.createStatement();
            ResultSet rs = stmnt.executeQuery(theQuery);

            if (rs.next()) {
                maxID = rs.getInt("MAX");
            }

            rs.close();
            stmnt.close();
            _con.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return maxID;
    }

}

Leaflet API Change

Looks like Leaflet changed the API for adding Openstreetmap to your map in Javascript. I had to make the following changes today:

From:

          L.tileLayer('https://{s}.tiles.mapbox.com/v3/{id}/{z}/{x}/{y}.png', {
                        maxZoom: 18,
                        attribution: 'Map data © +
                        <a href="http://openstreetmap.org">OpenStreetMap</a> contributors, ' +
                        '<a href="http://creativecommons.org/licenses/by-sa/2.0/">CC-BY-SA</a>, ' +
                        'Imagery © <a href="http://mapbox.com">Mapbox</a>',
                        id: 'examples.map-i875mjb7'
                }).addTo(map);


To:


                L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
                    attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
                }).addTo(map);

Satellite Map

This is a fun map to look at. You can zoom, pan and change the view angle. Click on a point to identify the satellite.

Project Sunroof

Google's come out with a pretty slick application to determine potential savings by changing to solar. Check it out here.

File Permissions When Logging Onto AWS

When logging onto my AWS instance with the following command:

ssh -i arcgis-HGL-2.pem arcgis@52.5.49.204

I got the following error:

Dave$ ssh -i arcgis-HGL-2.pem arcgis@52.5.49.204

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

@         WARNING: UNPROTECTED PRIVATE KEY FILE!          @

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Permissions 0644 for 'arcgis-HGL-2.pem' are too open.

It is required that your private key files are NOT accessible by others.

This private key will be ignored.

bad permissions: ignore key: arcgis-HGL-2.pem

Permission denied (publickey).

The solution was:

chmod 400 arcgis-HGL-2.pem 

HOLLIS+ Now Has Geographic Extent

The Harvard Library Catalog now has data extent for part of the collection. This was made possible by the extensive metadata developed for the Harvard Geospatial Library.

Test it yourself here.

The map inset uses the Leaflet Javascript library.

The Harvard Map Collection published a Sea Atlas viewer. It's fun to explore. Check it out here. The map uses the Leaflet Javascript library and is placed in the public domain. See it on GitHub.

Raster Performance: OpenLayers vs. Leaflet

I'm starting to make general comparisons of the performance speed between Leaflet and OpenLayers for displaying raster data from my spatial data repository. The stack includes an ArcSDE 9.3.1 instance running on RHEL and Oracle 11g. I'm using Geoserver to hit the database and serve images as WMS requests. I've embedded two web pages here as iFrames for experimentation. Among many factors that can impact speed, embedding an API in a complex web mapping application can affect performance differently, especially when other APIs like jQuery are used in the same web space.

Leaflet:
OpenLayers

General Observations on the Two APIs

I've found Leaflet to be much easier to understand and use. It's a smaller, newer Javascript library and is more intuitive and user than OpenLayers, and I have been using OpenLayers for at least 5 years. OpenLayers is an excellent API for web mapping but I have found its syntax difficult to understand and follow. It's a very large API and implements a lot of heavy GIS functionality. That's great, but not always needed in fast web mapping scenarios. OpenLayers is not as intuitive to me, and it takes me longer to generate simple map examples in OpenLayers than it does with Leaflet, especially if I have multiple data formats and mixed projections.

iFrames created using iframe Generator Tool

Nix Stuff I Always Forget

Bash .profile additions

Count all files in the current directory

• alias count='ls -l | wc -l'

Count all the xml files

• alias countxml='ls -l *.xml | wc -l'

Remove all files of type "xml" from the current directory

• alias remallxml="find . -maxdepth 1 -name '*.xml' -delete"

"cat" files and "grep" contents while listing the name of the file (in this case "huxley")

• cat * | grep "hux" *

More friendly size listing with ls -l
Remove all files More user friendly way to see file sizes with ls -l

• ls -l --block-size=MB
• du -h /folder

Plate Carree: Geoserver and ArcIMS Compatibility

Anyone trying to connect Geoserver to an ArcSDE dataset stored as (ESRI) EPSG: 54001 will quickly find that not all Plate Carree's are created equal. Geoserver does not like ESRI's choice of ellipsoid, which means tweaking the parameters slightly. 

Follow these simple steps to make EPSG:54001 operational in Geoserver.
1. Edit ../webapps/geoserver/data/user_projections/epsg.properties in your Tomcat context.
2. Add a new line at the end of the file and append the following text as 1 line. Syntax is critical.

54001= PROJCS["WGS 84 / Plate Carree", GEOGCS["WGS 84", DATUM["World Geodetic System 1984", SPHEROID["WGS 84", 6378137.0, 298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]], UNIT["degree", 0.017453292519943295], AXIS["Geodetic longitude", EAST], AXIS["Geodetic latitude", NORTH], AUTHORITY["EPSG","4326"]], PROJECTION["Equidistant Cylindrical (Spherical)", AUTHORITY["EPSG","9823"]], PARAMETER["central_meridian", 0.0], PARAMETER["latitude_of_origin", 0.0], PARAMETER["standard_parallel_1", 0.0],PARAMETER["false_easting", 0.0],PARAMETER["false_northing", 0.0],UNIT["m", 1.0],AXIS["Easting",EAST],AXIS["Northing", NORTH],AUTHORITY["EPSG","54001"]]

3. Restart Geoserver.
4. In your Geoserver Admin page select "Demos" and click on the "SRS List" link.
5. Search for either "54001" or "Plate Carree" and view the results.


Your projection should be in this list. Remember to keep an eye out on the Geoserver log for errors.

Generating Heat Maps for Point Data Using OpenLayers and Solr

Something that's been absent from OpenLayers for some time was a reliable and simple method of heat mapping smaller volumes ( ~25,000 records) of point data. What follows is a an example implementation that I've set up for testing purposes with the goal of integration into the Harvard Geospatial Library. The data used is a set of geotagged records from Harvard's HOLLIS catalog. While testing the heat mapping functionality could be a lot simpler by using an array of points, I chose to use data I had previously processed for another Library Project - Geotagging Catalog Records. So, I will start with an explanation of the process used to create that data source.

The records were pulled from Library Cloud using curl and saved as text files formatted as JSON.

Pulling data for "Joshua Chamberlain":

curl -v -o chamberlainandbowdoin0_end.json --basic -u xx:xx 'http://localhost:8080/solr/lccore/select?

indent=on&version=2.2&q=collection:hollis_catalog+AND+keyword:chamberlain+AND+keyword:bowdoin&start=0&rows=2

500&fl=call_num,creator,creator_exact,creator_keyword,data_source,dataset_tag,format,height,height_numeric,h

olding_libs,id,id_inst,id_isbn,id_lccn,id_oclc,keyword,language,lcsh,lcsh_exact,loc_call_num_sort_order,loc_

call_num_subject,note,note_keyword,online_avail,pages,pages_numeric,pub_date,pub_date_numeric,pub_location,p

ublisher,rsrc_key,rsrc_value,score_checkouts_undergrad,score_checkouts_grad,score_checkouts_fac,score_reserv

es,score_recalls,score_course_texts,score_holding_libs,score_extra_copies,score_total,shelfrank,sub_title,su

bject_keyword,title,title_exact,title_keyword,title_link_friendly,title_sort,toc,url,ut_count,ut_id,wp_categ

ories,wp_categories_exact,collection,520a,lcsh_keyword,score&wt=json&omitHeader=true'

The JSON output looks like this:

The JSON is then run through MetaCarta's geotagger:

geotag chamberlainandbowdoin0_end.json -d chamberlainandbowdoin0_end.tagged -e 

The output is XML and looks like this:

From here we parse the data using a ruby script and generate both XML and .csv files. We match the geotagged data with it's original input IDs to reconstruct the original Library Cloud record, adding the geotagged data to it.

The data is imported to Solr (blog post to follow) and has the following format.

From here we use AJAX to retrieve data from Solr and push it through OpenLayers to create a heat map. The heat map code class used can be found here.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<title>OpenLayers Heatmap Layer (using &lt;canvas&gt;)</title>
<script src="http://openlayers.org/api/OpenLayers.js" type="text/javascript"></script>
<script src="HeatmapLayer.js" type="text/javascript"></script>

</head>

<body>
    <h3>Solr Search - HEATMAP2-1</h3>

    Query: <input id="query" /> 
    <button id="search">Search</button>
    <hr/>
    <div id="map" style='height:400px;width=400' ></div>
    <hr/>
    <div id="results">
    </div>
</body>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

<script>

 function on_search(data) {

  var query = $('#query').val();
         if (query.length == 0) {
             return;
     }

   var url = "http://sanger.hul.harvard.edu:8080/solr/ghcore/select?indent=on&version=2.2&q="+query+"&start=0&rows=9000&fl=Geocoded_Field,title,id_inst,Anchor,cLoc1,primLoc&wt=json";

   var ajaxParams = 
     {
       type: "GET",
       url: url,
       dataType: 'jsonp',
       jsonp: 'json.wrf',
           crossDomain: true,
       success: function(data){
           on_data(data);
         },
       error: function(arg){
           errorFunction(arg);
         }
     };
   $.ajax(ajaxParams);
 }

    function on_data(data) {
    

  // Remove the heat2 layer if it already exists so that they do not overlap
  if (this.map.getLayersByName("Heatmap2")[0] !== undefined)
        this.map.removeLayer(this.map.getLayersByName("Heatmap2")[0]);
      
     var heat2 = new Heatmap.Layer("Heatmap2");
     console.log(query);

        $('#results').empty();
        var docs = data.response.docs;
        var total = 'Found ' + docs.length + ' results';
        $('#results').prepend('<div>' + total + '</div>');
        
        var coordinates = [
   [ 54.7408333333,9.43472222222 ],
   [ 55.4700375,19.51416 ]
  ];
  
  $.each(docs, function(j, item) {
   //console.log(item.title+"  "+item.primLoc);
   if (item.primLoc != null) {
          var junk = item.primLoc.split(",");
          var x = parseFloat(junk[0].trim());
          var y = parseFloat(junk[1].trim());
    coordinates.push([x,y]);
   }
  });
  
  for (var latlng in coordinates) {
    var point = new Heatmap.Source(new OpenLayers.LonLat(coordinates[latlng][1], coordinates[latlng][0]));
    heat2.addSource(point);
  }
  
  heat2.defaultIntensity = 0.1;
  heat2.setOpacity(0.33);
     map.addLayer(heat2);
     map.zoomToExtent(heat2.getDataExtent());
     
}

    function on_ready() {
        $('#search').click(on_search);
        /* Hook enter to search */
        $('body').keypress(function(e) {
            if (e.keyCode == '13') {
                on_search();
            }
        });
        
  map = new OpenLayers.Map('map', {
                    controls: [
                        new OpenLayers.Control.Navigation(),
                        new OpenLayers.Control.PanZoomBar(),
                        new OpenLayers.Control.LayerSwitcher({'ascending':false}),
                        new OpenLayers.Control.MousePosition(),
                    ],
  });

  // MAP STUFF HERE - Populate an initial set to view.

    heat.addSource(new Heatmap.Source(new OpenLayers.LonLat(9.434, 54.740)));
    heat.addSource(new Heatmap.Source(new OpenLayers.LonLat(9.833, 54.219)));

  var wms = new OpenLayers.Layer.WMS("OpenLayers WMS", "http://labs.metacarta.com/wms/vmap0", {layers: 'basic'});
   layer = new OpenLayers.Layer.WMS( "OpenLayers WMS",
                    "http://vmap0.tiles.osgeo.org/wms/vmap0", {layers: 'basic'} );
  map.addLayers([layer, heat]);
  map.zoomTo(2);

    }

 var map;
 var heat = new Heatmap.Layer("Heatmap");
    $(document).ready(on_ready);
</script>

Formatting from HiLite.Me.

The result allows me to test the display of geotagged HOLLIS records based on a Solr query such as "title_key:chamberlain".

While not always available, you can try it yourself here. (http://sanger.hul.harvard.edu:8080/geohollis/heat2.html)


Next Steps

• Add the point data to Solr 5.1 as an RPT type using WKT
• Test the mapping against 1 million+ records

Live View: