2

I used a modified version of the scripts at: https://bitbucket.org/dirkcgrunwald/fcc-uls to pull the FCC databases down and put them into a PostGIS environment.

I created a view so I didn't have to build a query every time I wanted something. No doubt it'll evolve over time as I'm pretty green to this.

SELECT row_number() OVER () AS oid,
fr.unique_system_identifier,
en.call_sign,
en.entity_name,
fr.frequency_assigned,
fr.frequency_upper_band,
(lo.lat_degrees + lo.lat_minutes / 60)::numeric + lo.lat_seconds / 3600::numeric AS latitude,
((lo.long_degrees + lo.long_minutes / 60)::numeric + lo.long_seconds / 3600::numeric) * (-1)::numeric AS longitude,
fr.power_output,
fr.power_erp
   FROM pubacc_fr fr
     JOIN pubacc_lo lo ON fr.unique_system_identifier = lo.unique_system_identifier
     JOIN pubacc_en en ON fr.unique_system_identifier = en.unique_system_identifier
  WHERE fr.class_station_code = 'WDX'::bpchar;

I added the row_number bit in an attempt to have a column with a unique ID so that I could open the table in ArcMap. So far my attempts have been futile. While a select * from weather_radar; does indeed show the OID column, I don't see it when I browse the view in phpPgAdmin and ArcMap doesn't see it either when I connect to it.

Thoughts?

My drive to open the attribute table is to figure out why lat\long are limited to five digits when there's at least a dozen showing in phpPgAdmin when I browse the view. This has the unfortunate side-effect of lumping all points in a given metro into a small area nowhere near the correct point for any of them.

  • 1
    Not what you are asking, but perhaps QGIS (which was built to view PostGIS data) would meet your needs: qgis.org/en/site – HeikkiVesanto Dec 12 '16 at 15:02
  • I think I have QGIS installed, so I can look. However, ArcMap is seeing the same thing I see when I "browse" the view in phpPgAdmin. I don't know if that trail will reveal much. – Mike Hammett Dec 12 '16 at 15:21
1

Using row_number() to populate a rowid column is standard practice, but only if:

  1. The column is defined as (or cast to) an integer (ArcGIS does not support bigint data, and will ignore all tables which contain forbidden types)

  2. The value is used to populate an actual table or materialized view.

It is not acceptable to use row_number() in a view or query to simulate an actual rowid, since ArcGIS requires that the value be reproducible in subsequent queries -- Since the identity of row "1" changes with query constraints, there is no way to use the rowid as a link between the unbounded table and a selected subset (which is the purpose of the rowid column).

Since best practice requires storing an actual geometry instead of a dynamically calculated one (so an index can be built), you'll need to manifest the geometry (or geography) column as well.

Once you have a valid table definition with a true geometry column, you can review the longitude encoding formula by comparing the parameters with the output of ST_X(), where I expect you will find that casting to numeric instead of double precision or a named numeric(12,6) is causing your precision truncation (and you likely have a sign issue which inverts the minutes and seconds as well).

I strenuously recommend that you adopt a "lon,lat" namimg scheme over "long,lat", partially because the member name lengths will be even, but mostly to avoid name collision with the "long" datatype, and to reinforce the fact that longitude is an X quantity.

  • I used the full words latitude and longitude. lat and long are from the tables themselves, which are sourced from the FCC. As they have known bad records in the database go uncorrected, I doubt they'll change the column name. It's nice to know for my own use, though. Any changes I make to the tables are overwritten the next time I pull an update from the FCC, so I can't make any permanent changes. Each time I import, should I add a geometry column to the tables that have geometry? Should I add a rowid column to each table as well? How would I determine which rowid to map through to the view – Mike Hammett Dec 13 '16 at 14:36
  • I'll look at the supported data types and adjust. Are you suggesting I use integer instead of bigint? I'll also look at using a named numeric (say 12,9) instead of just numeric. I didn't actually put numeric in there, phpPgAdmin decided that for me. – Mike Hammett Dec 13 '16 at 14:39

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.