Extract PDF content (from CHP) with Python

import re

import sys

import urllib.request

import pdfplumber

import pandas as pd

if (len(sys.argv) < 2):

    print("\n\nSyntax: python extract_pdf.py date_string (e.g. 20220124) \n\n")

    exit()

def main():    

    date_string  = sys.argv[1] # e.g. 20220124

    pdf_file_name = f"ctn_{date_string}.pdf" # PDF from CHP: https://www.chp.gov.hk/files/pdf/ctn_20220124.pdf

    #download_pdf( pdf_file_name )

    extract_pdf( pdf_file_name )

def extract_pdf( pdf_file_name ):

    with pdfplumber.open( f"./pdf/{pdf_file_name}" ) as pdf:

        for page in pdf.pages:

            #print(page)

            for table in page.extract_tables():

                df = pd.DataFrame(table[1:], columns=table[0])

                for index, row in df.iterrows():

                    if (isinstance(row[0], str) and len(row[0])>0):

                        rowid = row[0].replace(".","")

                        title = row[1].split("\n")

                        for i in range(0,len(title)):                            

                            if (re.search(u'[\u4e00-\u9fff]', title[i]) is None):

                                title[i] = ""

                        print(rowid, "".join(title))

def download_pdf ( pdf_file_name ) :

    pdfFile = urllib.request.urlopen(f"https://www.chp.gov.hk/files/pdf/{pdf_file_name}")

    file = open(f"./pdf/{pdf_file_name}", "wb")

    file.write(pdfFile.read())

    file.close()

main()

Display cx_oracle error indicating which rows are affected

cursor.executemany("SQL EXEC STATEMENT", data, batcherrors=True)

for error in cursor.getbatcherrors():

    print("Error", error.message, "at row offset", error.offset) 

Calculate the viewing location in Cesium

 Here I summarise two approach for calculating the viewing location in Cesium viewer:

Method 1 (using Camera.computeViewRectangle() method) 

var projection = new Cesium.WebMercatorProjection();

var rect = new Cesium.Rectangle()

viewer.camera.computeViewRectangle(projection.ellipsoid, rect)

let rect_x = (rect.east - rect.west)/2 + rect.west;

let rect_y = (rect.north - rect.south)/2 + rect.south;

var rect_lon = Cesium.Math.toDegrees(rect_y);

var rect_lat = Cesium.Math.toDegrees(rect_x);

console.log(rect_lat, rect_lon)

Note: The computeViewRectangle( ) could be very large when the view pitch is very flat, resulting in a very far point being calculated.

Method 2 (using Camera.computeViewRectangle() method) 

var cc = document.getElementById("cesiumContainer");

var screenCenterPt = new Cesium.Cartesian2(cc.offsetWidth/2, cc.offsetHeight/2);

var pick = viewer.scene.globe.pick(viewer.camera.getPickRay(screenCenterPt), viewer.scene);

if (pick) {

  var geoPt = viewer.scene.globe.ellipsoid.cartesianToCartographic(pick);

  var point = [geoPt.longitude/ Math.PI * 180, geoPt.latitude/ Math.PI * 180];

  

  var rect_lon = point[0];

  var rect_lat = point[1];

  

  console.log(rect_lat, rect_lon);

}

Note: It uses screen center to calculate the point, seems to be more useful.

DOS batch file: Expand * into list of filenames in command line

expand-wildcard.bat

@echo off

setlocal EnableDelayedExpansion

set params=command

for %%a in ("%~1") do (

    set params=!params! %%a

)

echo %params%

%params%

 

Then, running the following command:

C:\>expand-wildcard filename*

Will be executed as following:

C:\>command filename1 filename2 filename3 filename4 ..

SSH slow connection issues

When I connect to a Linux machine via ssh, it waits a long time (~ 10secs) before showing me the password prompt.

After some research, I found that it's caused by the GSS API authentication issue. Some suggestions to solve this issues are listed below (either one of this will work):

1. Disable the GSS API authentication in sshd:
   

           Modify the file "/etc/ssh/sshd_config", disable the "GSSAPIAuthentication" by:

   GSSAPIAuthentication no 

    

2. Disable GSS API authentication in PuTTY:

        Connection > SSH > Auth > GSSAPI

                    Uncheck "Attempt GSSAPI authentication (SSH-2 only)" 

 

 

Disabling GeoNetwork XSL caching

In GeoNework (3.x), the XSL caching is enabled by default. To disable this, we can modify the following:

In the ZIP GeoNetwork version, find the file "<GeoNetwork-Directory>/web/geonetwork/WEB-INF/classes/META-INF/services/javax.xml.transform.TransformerFactory"

This file should contains the following:

    de.fzi.dbs.xml.transform.CachingTransformerFactory

Change this to the following:

    net.sf.saxon.TransformerFactoryImpl

Then the XSL transformation will not be cached.

Reference: http://mapas.mda.gov.br/geonetwork/docs/eng/users/quickstartguide/installing/index.html

Oracle TO_DATE( ) this afternoon

Just being asked to convert string "1/1/2020 0:00' to date field in Oracle, and come up with this:

select to_date('1/1/2020 0:00', 'dd/mm/yyyy hh24:mi') from dual

Tips on GeoPandas

According to the documentation in GeoPandas, the following codes could read a local GeoJSON file and import to a PostGIS DB:

import geopandas

from sqlalchemy import create_engine

path_to_data = "./my_file.geojson"

gdf = geopandas.read_file(path_to_data)

db_connection_url = "postgres://user:pwd@localhost:5432/db";

engine = create_engine(db_connection_url)

gdf.to_postgis("my_file_table", con=engine)

I found the following tips:

• "postgres://" has to be changed to "postgresql://". 
• Package "psycopg2" is required together with "sqlalchemy" (Details could be found in DBAPI support section in PostgreSQL documentation)
• Package "GeoAlchemy2" is required together with "sqlalchemy"

Enjoy!

About CKAN installation from package

 In lubuntu 20.04, we can install CKAN from package via the following command:

# dpkg -i python-ckan_2.9-py3-focal_amd64.deb

The system fails with the following error:

ModuleNotFoundError: No module named 'distutils.core'

This error is caused by missing some essential Python modules, to solve this problem, install the 'python3-distutils' package by:

sudo apt-get install python3-distutils

 

About CKAN Sample Dataset

You can use "ckan seed" to create sample datasets

e.g. ckan seed basic, which creates two datasets: 

1. annakarenina
2. warandpeace

BUT, after you delete the two datasets, either via 

User Interface 

OR 

CLI: 

ckan dataset delete annakarenina 

ckan dataset purge annakarenina

 

You cannot run the "ckan seed basic" again to regenerate the two datasets. 

Why?

Since there are two records still in table "group" and 5 records created in table "user" (They are created by "ckan seed basic"). Remove those records and then you can run the "ckan seed basic" again.