1

I'm using the below code to generate GeoJSON from a Postgres95 table. When parsing out samples eg a months worth of crime data, which should be ~20k records for the month of March 2016, I'm getting a huge number of blank records and a few occasional populated ones.

     SELECT row_to_json(fc)
 FROM (
   SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features
   FROM (
     SELECT
       'Feature' AS type,
       ST_AsGeoJSON(ST_Transform(t.geom,4326))::json AS geometry,
       row_to_json((SELECT p FROM (
         SELECT
          t.reported_date,
          t.report_num,
          t.occurred_date,
          t.occurred_time,
          t.agency_area_id,
          t.agency_area_name,
          t.agency_rd,
          t.crime_code,
          t.crime_desc,
          t.status_id,
          t.status_desc,
          t.location,
          t.cross_street,
          t.agency_id,
          t.sys_period_start,
          t.geom
         WHERE
              occurred_date >= '2016-03-01'
          AND occurred_date <  '2016-03-31'
         ) AS p)) AS properties
     FROM mySchema.myTable AS t
     WHERE t.crime_code is not null
     ORDER BY 1
     LIMIT 2000
   ) AS f
 ) AS fc;

This is what I get in return where 95% of the rows are returning as blank. The geom data - when parsed down to -118.0000, 33.0000 numbers can be found in the original csv data so it looks like the points are coming through, just not the properties?

{
"type": "FeatureCollection",
"features": [{
    "geometry": {
        "type": "Point",
        "coordinates": [-118.396003723145, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.274002075195, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.304000854492, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.274002075195, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.305000305176, 33.9546012878418]
    },
    "type": "Feature",
    "properties": {
        "crime_desc": "BURGLARY",
        "occurred_date": "2016-03-16",
        "agency_area_id": "12",
        "reported_date": "2016-03-16",
        "cross_street": "",
        "status_id": "IC",
        "sys_period_start": "",
        "agency_area_name": "77th Street",
        "status_desc": "Invest Cont",
        "occurred_time": "800",
        "agency_id": "CA1942",
        "agency_rd": "1273",
        "crime_code": "310",
        "location": "9127 S DENKER AV",
        "geom": "0101000020E61000000000002085935DC00000006030FA4040",
        "report_num": "161209376"
    }
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.305999755859, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.277000427246, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.277000427246, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.274002075195, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.274002075195, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}, {
    "geometry": {
        "type": "Point",
        "coordinates": [-118.276000976562, 33.9546012878418]
    },
    "type": "Feature",
    "properties": null
}]

}

I thought this was a coalescing issue and added the two rows for cols that may have null or no values. After doing that I still get the same results. When looking at several rows where the GeoJSON was created I cannot find any discernible pattern.

       row_to_json((SELECT p FROM (
     SELECT
       COALESCE( NULLIF(t.cross_street,'') , '0' ),
       COALESCE( NULLIF(t.sys_period_start,'') , '0' ),           
      t.reported_date,
      t.report_num,

Here's my table:

 CREATE SEQUENCE mySchema.crime_id_seq
  START WITH 100001
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

CREATE TABLE mySchema.myTable (
id bigint NOT NULL DEFAULT nextval('mySchema.crime_id_seq'::regclass),
reported_date     character varying,
report_num        character varying,
occurred_date     character varying,
occurred_time     character varying,
agency_area_id    character varying,
agency_area_name  character varying,
agency_rd         character varying,
crime_code        character varying,
crime_desc        character varying,
status_id         character varying,
status_desc       character varying,
location          character varying,
cross_street      character varying,
agency_id         character varying,
sys_period_start  character varying,
geom              character varying

CONSTRAINT crime_pk PRIMARY KEY (id)
);


|reported_date|report_num|occurred_date|occurred_time|agency_area_id|agency_area_name|agency_rd crime_code|crime_desc|status_id|status_desc|location|cross_street|lon_lat|agency_id|sys_period_start|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|2016/11/29|161228682|2016/10/14|1500|12|77th Street|1211|649|DOCUMENT FORGERY / STOLEN FELONY|IC|Invest Cont|CRENSHAW BL|54TH ST|CA1942||(-118.2983, 34.0391)|
|2016/02/01|161406199|2016/02/01|840|14|Pacific|1454|310|BURGLARY|IC|Invest Cont|4771 LA VILLA MARINA|CA1942||(-118.3515, 34.0991)|
|2016/11/17|161119346|2016/11/08|1800|11|Northeast|1127|354|THEFT OF IDENTITY|IC|Invest Cont|5730 FAYETTE ST|CA1942||(-118.3951, 34.0391)|

1 Answer 1

0

Ok, so I'm replacing my answer with a working query. My schema is named different, but you should be able to sort it out.

SELECT row_to_json(fc)
FROM (
    SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features
    FROM (
        SELECT
            'Feature' AS type,
            ST_AsGeoJSON(m.geom)::json AS geometry,
            row_to_json(p) as properties 
        FROM
            public.myTable as m
        JOIN (
            SELECT
                t.reported_date,
                t.report_num,
                t.occurred_date,
                t.occurred_time,
                t.agency_area_id,
                t.agency_area_name,
                t.agency_rd,
                t.crime_code,
                t.crime_desc,
                t.status_id,
                t.status_desc,
                t.location,
                t.cross_street,
                t.agency_id,
                t.sys_period_start
            FROM myTable as t
            ) AS p
        ON p.report_num = m.report_num
        WHERE 
            m.crime_code is not null 
            AND m.occurred_date >= '2016-10-01'
            AND m.occurred_date <  '2016-10-31'
        ORDER BY 1
        LIMIT 2000
    ) AS f
) AS fc;

This gives a result of:

{
  "type": "FeatureCollection",
  "features": [
    {
      "geometry": {
        "type": "Point",
        "coordinates": [
          -118.2983,
          34.0391
        ]
      },
      "type": "Feature",
      "properties": {
        "crime_desc": "DOCUMENT FORGERY \/ STOLEN FELONY",
        "occurred_date": "2016\/10\/14",
        "agency_area_id": "12",
        "reported_date": "2016\/11\/29",
        "cross_street": null,
        "status_id": "IC",
        "sys_period_start": null,
        "agency_area_name": "77th Street",
        "status_desc": "Invest Cont",
        "occurred_time": "1500",
        "agency_id": null,
        "agency_rd": null,
        "crime_code": "649",
        "location": "CRENSHAW BL 54TH ST",
        "report_num": "161228682"
      }
    }
  ]
}
7
  • Thanks for the help Alex. When I try that I get this response: ERROR: subquery must return only one column LINE 8: row_to_json((SELECT * FROM ( ^ ********** Error ********** ERROR: subquery must return only one column SQL state: 42601 Character: 248
    – civarchive
    Mar 21, 2017 at 22:52
  • I've updated the statement in my reply. I think you'll need to play with that inner SQL section. Mine might work!
    – Alex Leith
    Mar 21, 2017 at 22:59
  • Ok tried that and am getting this error: ERROR: syntax error at or near "FROM" LINE 30: FROM poursafe.ca_crime AS t Here's the create stmt. I had to gut rows due to char limit: CREATE TABLE mySchema.myTable ( id bigint NOT NULL DEFAULT nextval('mySchema.crime_id_seq'::regclass), reported_date character varying, occurred_date character varying, crime_code character varying, agency_id character varying, sys_period_start character varying, geom character varying CONSTRAINT crime_pk PRIMARY KEY (id) );
    – civarchive
    Mar 21, 2017 at 23:39
  • Can you add this to your question? I'd like an insert for a row, plz!
    – Alex Leith
    Mar 21, 2017 at 23:44
  • Ok rows added, had to fake - (lon,lat) with nearby coords. Thanks again.
    – civarchive
    Mar 22, 2017 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.