Civica Contact 360 (formerly Asidua CCP)

System Category	CRM
Source	Civica Contact 360 (formerly Asidua CCP)
Available since	2014
Organisation

Civica Contact 360 (formerly Asidua's CCP) for Local Government provides location based reporting such as Flytipping, Street lighting etc. Asidua requires the following from iShare / Logger:

Base mapping configured as a WMS
Overlays configured as a WMS and WFS
Expose Nearest Address and Nearest Street Web Service

Base mapping configured as a WMS

If using a hosted base mapping service such as Astun Data Services - ADS with iShare then this can normally be used by CCP. If iShare is configured to use local base mapping then an iShare base map can be exposed as a WMS in the same way that Overlay MapSources are configured as detailed in Configure MapServer to be a WMS or WFS Server.

Overlays configured as a WMS and WFS

A MapServer mapfile should be configured containing all required overlays setup so that all layers are available via WFS as detailed in Configure MapServer to be a WMS or WFS Server. It is important to ensure that all attributes are exposed. An iShare MapSource must be created that is associated with the mapfile. As the MapSource will not be used within iShare directly there is no need to create layer groups or layers for the layers in the mapfile. Asidua requires a 'streets' layer to be available via WFS/WMS. It is also used in the web services below.

Asidua will require the GetCapabilities URLs for the WMS and WFS services. Assuming the MapSource is called Logger example requests would be:

WMS example request

https://example.com/GetOWS.ashx?VERSION=1.1.0&MAPSOURCE=Example/LoggerOWS&SERVICE=WMS&REQUEST=GetCapabilities

WFS example request

https://example.com/GetOWS.ashx?VERSION=1.1.0&MAPSOURCE=Example/LoggerOWS&SERVICE=WFS&REQUEST=GetCapabilities

Expose Nearest Address and Nearest Street Web Service

Web Services for finding the nearest address and street to a given coordinate can be exposed by iShare by creating an Internal Function DataShare Connection.

Create the SQL functions in the Data Share database

The following SQL functions can be used as a template:

Generic find nearest function used to generate SQL based on supplied parameters

-- Function: at_wkf_custom_nearest_sql(numeric, numeric, numeric, integer, text, text, text, text)
-- DROP FUNCTION at_wkf_custom_nearest_sql(numeric, numeric, numeric, integer, text, text, text, text);
CREATE OR REPLACE FUNCTION at_wkf_custom_nearest_sql(x numeric, y numeric, radius numeric, maxfeatures integer, "schema" text, tbl text, cols text, geom_col text)
  RETURNS text AS
$BODY$
    DECLARE
        getstatement text;
        sourcerecord record;
    BEGIN
        getstatement:='SELECT ' || cols || ' from (SELECT *, ST_X(ST_PointOnSurface(' || quote_ident(geom_col) || '))::text || '','' || ST_Y(ST_PointOnSurface(' || quote_ident(geom_col) || '))::text as _point from (select ' || quote_ident(schema) || '.' || quote_ident(tbl) || '.*, p.*, least(' || radius || ', round(st_distance(' || quote_ident(schema) || '.' || quote_ident(tbl) || '.' || quote_ident(geom_col) || ', p.geom))) as _distance from ' || quote_ident(schema) || '.' || quote_ident(tbl) || ' inner join (select ST_SetSRID(ST_MakePoint(' || x || ', ' || y || '), (select ST_SRID(' || quote_ident(geom_col) || ') from ' || quote_ident(schema) || '.' || quote_ident(tbl) || ' limit 1)) as geom) as p on (' || quote_ident(schema) || '.' || quote_ident(tbl) || '.' || quote_ident(geom_col) || ' && ST_Expand(p.geom, ' || radius || ')) order by _distance) as foo limit ' || maxfeatures || ') as bar;';
        RETURN getstatement;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION at_wkf_custom_nearest_sql(numeric, numeric, numeric, integer, text, text, text, text) OWNER TO postgres;
COMMENT ON FUNCTION at_wkf_custom_nearest_sql(numeric, numeric, numeric, integer, text, text, text, text) IS 'Reserved - Astun Technology DataShare Internal Function
Params:8
Param1:Number:x:307888:The easting.
Param2:Number:y:194675:The northing.
Param3:Number:radius:50:The radius to search in meters.
Param4:Number:maxfeatures:10:The maximum number of features to return.
Param5:Text:schema:public:The schema of the table.
Param6:Text:tbl:poi:The table name to search.
Param7:Text:cols:ogc_fid:Comma seperated list of columns to return from table as a string.
Param8:Text:geom_col:wkb_geometry:Name of the geometry column.
Comment:Finds Features from table within x meters, includes optional ''_point'' which falls within each feature and a ''_distance'' columns containing the distance from the specifed x/y which can be included in the resultset if specified in the cols list.
';

Find nearest address function querying data from AstunLocationLookup

-- Function: at_intfn_custom_nearest_address(text, text, text, text)
-- DROP FUNCTION at_intfn_custom_nearest_address(text, text, text, text);
CREATE OR REPLACE FUNCTION at_intfn_custom_nearest_address(x text, y text, radius text, maxfeatures text)
  RETURNS SETOF record AS
$BODY$
    DECLARE
        getstatement text;
        sourcerecord record;
    BEGIN
        EXECUTE $$select * from at_wkf_custom_nearest_sql($$ || cast(x as numeric) || $$, $$ || cast(y as numeric) || $$, $$ || cast(radius as numeric) || $$, $$ || cast(maxfeatures as int) || $$, 'public', 'AstunLocationLookup', '"UniqueId", "Name", _point, _distance', 'wkb_geometry')$$ INTO getstatement;
        FOR sourcerecord IN EXECUTE getstatement LOOP
            RETURN NEXT sourcerecord;
        END LOOP;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION at_intfn_custom_nearest_address(text, text, text, text) OWNER TO postgres;
COMMENT ON FUNCTION at_intfn_custom_nearest_address(text, text, text, text) IS 'Reserved - Astun Technology DataShare Internal Function
Params:4
Param1:Text:x:307888:The easting.
Param2:Text:y:194675:The northing.
Param3:Text:radius:50:The radius to search in meters.
Param4:Text:maxfeatures:10:The maximum number of features to return.
Comment:Finds Features from AstunLocationLookup within x meters, includes ''_point'' which falls within each feature and a ''_distance'' columns containing the distance from the specifed x/y.
';

Find nearest street function querying data from a traffic_transport.streets table

-- Function: at_intfn_custom_nearest_street(text, text, text, text)
-- DROP FUNCTION at_intfn_custom_nearest_street(text, text, text, text);
CREATE OR REPLACE FUNCTION at_intfn_custom_nearest_street(x text, y text, radius text, maxfeatures text)
  RETURNS SETOF record AS
$BODY$
    DECLARE
        getstatement text;
        sourcerecord record;
    BEGIN
        EXECUTE $$select * from at_wkf_custom_nearest_sql($$ || cast(x as numeric) || $$, $$ || cast(y as numeric) || $$, $$ || cast(radius as numeric) || $$, $$ || cast(maxfeatures as int) || $$, 'traffic_transport', 'streets', '"ogc_fid", "usrn", description, _point, _distance', 'wkb_geometry')$$ INTO getstatement;
        FOR sourcerecord IN EXECUTE getstatement LOOP
            RETURN NEXT sourcerecord;
        END LOOP;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION at_intfn_custom_nearest_street(text, text, text, text) OWNER TO postgres;
COMMENT ON FUNCTION at_intfn_custom_nearest_street(text, text, text, text) IS 'Reserved - Astun Technology DataShare Internal Function
Params:4
Param1:Text:x:307888:The easting.
Param2:Text:y:194675:The northing.
Param3:Text:radius:50:The radius to search in meters.
Param4:Text:maxfeatures:10:The maximum number of features to return.
Comment:Finds Features from AstunLocationLookup within x meters, includes ''_point'' which falls within each feature and a ''_distance'' columns containing the distance from the specifed x/y.
';

Create the NearestAddress Data Share Connection

Create an Internal Function Data Share Connection called NearestAddress with the following details:

Name: NearestAddress
Display Name: NearestAddress
Help Info: as data("UniqueId" character varying(20), "Name" character varying, _point text, _distance double precision)

Create the NearestStreet Data Share Connection

And now similarly for NearestStreet with the following details

Name: NearestStreet
Display Name: NearestStreet
Help Info: as data("ogc_fid" int, "usrn" integer, "description" character varying(100), _point text, _distance double precision)

Call the GetData.aspx Web Service

Now that the Internal Function Data Share connections have been configured the data is accessible via a Data Share Data Request to the GetData.aspx Web Service. You will need to use the XSL DataFunc Request passing the parameters required by the functions you have just created e.g. x, y, radius and maxFeatures.

Assuming the Data Share Connections are called NearestAddress and NearestStreet, example requests would be:

Nearest 10 addresses within 5km of coordinates 487071, 233471

https://example.com/GetData.aspx?callback=results&RequestType=xsl&Type=jsonp&service=DataFunc&xsl=xml/atJSONDatatable.xsl&ds=NearestAddress&params=487071~233471~5000~10

Nearest 10 streets within 5km of coordinates 487071, 233471

https://example.com/GetData.aspx?callback=results&RequestType=xsl&Type=jsonp&service=DataFunc&xsl=xml/atJSONDatatable.xsl&ds=NearestStreet&params=487071~233471~5000~10