Martha,
I wish I could help with understanding why json export is working and csv
isn’t. Unfortunately, I can’t. That said, toward working with your json
output, you can identify the uuid of nodes/fields that store geometry with this
sql:
select
b.name as model_name,
a.name as node_name,
a.nodeid
FROM nodes a
join graphs b on a.graphid = b.graphid
where
datatype = 'geojson-feature-collection'
order by b.name, a.name
With that information, you can search for that uuid in your json to isolate
where geometry is stored in the json.
---
Adam Lodge
Farallon Geographics
> On Oct 2, 2019, at 4:24 PM, Martha S <[email protected]> wrote:
>
> One more bit of information: I was able to export the business data to json.
> Naturally, I did not find '|' in the resulting file.
>
> If this export includes geographies (or lack thereof, which is what I'm
> really after) for this resource model, I might be able to query what I need
> based on the tools you have already provided me here and export that to CSV.
>
> I'm still interested in any further guidance you might offer regarding my
> inability to export business data to csv, even as I pursue this new avenue.
>
> Thanks,
> Martha
>
>
> On Wednesday, October 2, 2019 at 3:37:45 PM UTC-7, Martha S wrote:
> Alas, Gentlemen, neither effort returned anything. In PGADMIN4, I ran both of
> the following with no results returned:
>
> SELECT
>
> a.resourceinstanceid,
> b.name <http://b.name/> as card_name
> FROM tiles a
> JOIN cards b on a.nodegroupid = b.nodegroupid
> WHERE 1=1
> and tiledata::text like '%|%'
>
> set standard_conforming_strings=on;
> SELECT
> a.resourceinstanceid,
> b.name <http://b.name/> as card_name
> FROM tiles a
> JOIN cards b on a.nodegroupid = b.nodegroupid
> WHERE 1=1
> and tiledata::text like E'%xa6%'
>
> In the python shell I ran the following with no results returned:
>
> from arches.app.models.tile import Tile
> tiles = Tile.objects.all()
> for tile in tiles:
> for value in tile.data.values():
> if isinstance(value, unicode):
> if "|" in value:
> print tile.resourceinstance_id
>
> At least you are agreed, there is no there there. Just for grins, I reran the
> export business data command and got the same error as shown in the first
> post in this thread. As a final effort, I ran the following and got more than
> 10,000 results, since that's all that stayed in the terminal memory:
>
> from arches.app.models.tile import Tile
> tiles = Tile.objects.all()
> for tile in tiles:
> for value in tile.data.values():
> if isinstance(value, unicode):
>
> print tile.resourceinstance_id, value
>
> Below is a partial list of the results. I don't see anything suspicious in
> the values, but I see that these few resources have multiple entries in this
> list. I don't know if that's because this list list is a complete dump of all
> 1M+ cards or if there's something to see here. I ran reports on a few of
> these and found nothing amiss.
>
> fd44f81a-14b1-41f9-b33a-d66c66a960ec 33595d18-c941-4a4b-91e8-0fa08e229c4d
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 3db588f4-2384-4cbe-b429-5c63630b9b9d
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 138B177 25
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 08130495-9729-46fa-8a3b-2dfa97286ab9
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 679152
> fd44f81a-14b1-41f9-b33a-d66c66a960ec 0997ad7a-ad94-43b3-b662-8945c6c1b10e
> fd44f81a-14b1-41f9-b33a-d66c66a960ec fd44f81a-14b1-41f9-b33a-d66c66a960ec
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 f5d15b7d-da92-4b63-82a1-8632b361e860
> fd44f81a-14b1-41f9-b33a-d66c66a960ec 3db588f4-2384-4cbe-b429-5c63630b9b9d
> fd44f81a-14b1-41f9-b33a-d66c66a960ec 117B181 568
> 99b6ae9f-a061-46f4-b69c-f6f4b43ad69a 6137 E GARRISON DR
> 99b6ae9f-a061-46f4-b69c-f6f4b43ad69a 209c5206-eb2f-406e-b42e-288f84680a4a
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 411 N SPAULDING AVE
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 209c5206-eb2f-406e-b42e-288f84680a4a
> fd44f81a-14b1-41f9-b33a-d66c66a960ec 3939 S URSULA AVE
> fd44f81a-14b1-41f9-b33a-d66c66a960ec 209c5206-eb2f-406e-b42e-288f84680a4a
> 29a5a1ce-bfd1-4f8a-90a8-41e76b337558 2608 S ORCHARD AVE
> 29a5a1ce-bfd1-4f8a-90a8-41e76b337558 ad7fce52-1185-4ad8-bc65-e100de19be96
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 Orange Grove Avenue-Gardner Street
> Multi-Family Residential Historic District - Contributor
> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 efd0fd4d-ebcf-4e7d-801f-0dc036a0c1b3
> fd44f81a-14b1-41f9-b33a-d66c66a960ec Baldwin Plaza-Sunset Fields Historic
> District - Contributor
> fd44f81a-14b1-41f9-b33a-d66c66a960ec efd0fd4d-ebcf-4e7d-801f-0dc036a0c1b3
>
> So, is there a middle ground here, where I can get more than nothing and less
> than all? Is there a way for me to log the bad records and continue with the
> export nonetheless?
>
> Somehow, I need to correct the issues and successfully export data from the
> database.
>
> Thanks in advance,
> Martha
>
>
> On Wednesday, October 2, 2019 at 10:40:31 AM UTC-7, Martha S wrote:
> Thank you,to both Adams.
>
> I will run both suggestions. Both are helpful to my ongoing database
> activities. The more approaches in my toolbox, the better.
>
> It is great to know that the cards can be treated as flat files with
> tiledata; I missed that somehow. Iterating through each record with the ORM
> approach in the python shell is a new entry point for me.
>
> I shall report back later today.
>
> Martha
>
>
>
> On Tuesday, October 1, 2019 at 10:57:40 PM UTC-7, Adam Cox wrote:
> Another approach would be to use the django ORM in the python shell.
>
> From your project run
>
> python manage.py shell
>
> Now you can paste this code in
>
> from arches.app.models.tile import Tile
> tiles = Tile.objects.all()
> for tile in tiles:
> for value in tile.data.values():
> if isinstance(value, unicode):
> if "|" in value:
> print tile.resourceinstance_id
> print value
>
> Adam
>
> On Tue, Oct 1, 2019 at 11:26 PM Adam Lodge <[email protected] <>> wrote:
> I realized that I inadvertently took this discussion offline to just Martha
> and I. Here's some missing thread:
>
> Martha,
>
> (I think) you can issue this sql statement against the Postgres database
> behind arches to identify the specific resource instance and “card” that
> contains a given offending character:
>
> SELECT
> a.resourceinstanceid,
> b.name <http://b.name/> as card_name
> FROM tiles a
> JOIN cards b on a.nodegroupid = b.nodegroupid
> WHERE 1=1
> and tiledata::text like '%|%’
>
> Note that the offending character you search for will be defined on the last
> line surrounded by wildcards.
>
> With the resourceinstanceid value, you can construct a url that will take you
> strait to the resource editor for that specific resource instance… like this:
> https://[hostname]/resource/[resourceinstanceid <>]
>
> You can use the card value to navigate to the specific card (or form) that
> has the field with the offending value.
>
> It’s a kinda manual approach to fixing them, but at least you can sniff them
> out with this approach.
>
> Best,
> Adam
>
> ---
> Adam Lodge
> Geospatial Systems Consultant
> Farallon Geographics
>
>> On Oct 1, 2019, at 7:10 PM, Martha Selig <[email protected] <>> wrote:
>>
>> Adam,
>>
>> I'm talking about the Arches database, though I am open to any suggestion as
>> to simplifying this task. I am unable to export to CSV because of this
>> symbol, but a flat file would definitely be the easiest way to look for and
>> replace/delete it no matter where it is found.I would like to deal with this
>> issue one time, if at all possible. We're starting a cycle of reviewing,
>> updating, and correcting data and I'm stuck at the starting gate.
>>
>> Tomorrow I'm going to try to query just the records needed for the first
>> pass -- resources that don't have geographies defined -- and see if I can
>> export those somehow. If I'm lucky, none of the records I need will have '|'
>> in them, so I can get a CSV. At least I can get the client going.
>>
>> Any help would be greatly appreciated. That would include, I suppose, some
>> info on modifying the export code to skip to the next entry when this error
>> is triggered. I haven't taken a look at the code to see how straightforward
>> that might be, but if I could log the problem record and keep on going, that
>> would be swell. I wasn't thinking I'd be altering core code but if that
>> keeps me going, why not?
>>
>> Thanks,
>> Martha
>>
>> On Tue, Oct 1, 2019 at 5:59 PM Adam Lodge <[email protected] <>> wrote:
>> Are you searching an Arches database, or just table in a given RDBMS, or a
>> flat file of some sort?
>>
>> ---
>> Adam Lodge
>>
>>> On Oct 1, 2019, at 2:49 PM, Martha S <[email protected] <>> wrote:
>>>
>>> Do anyone have a recommendation for the best way to go through the database
>>> of >110,000 records to find all instances of the '|' in any field it might
>>> occur? I am told this was the delimiter used in .arches files, so there
>>> could be other instances sprinkled throughout the database.
>>>
>>> Needle in a haystack time.
>>>
>>> Thanks,
>>> Martha
>
>
> On Tuesday, October 1, 2019 at 2:49:10 PM UTC-7, Martha S wrote:
> Do anyone have a recommendation for the best way to go through the database
> of >110,000 records to find all instances of the '|' in any field it might
> occur? I am told this was the delimiter used in .arches files, so there could
> be other instances sprinkled throughout the database.
>
> Needle in a haystack time.
>
> Thanks,
> Martha
>
> On Tuesday, October 1, 2019 at 10:35:47 AM UTC-7, Martha S wrote:
> Thank you, Alexi,
>
> I'll just have to hope that's the only "special" character in the data.
>
> Martha
>
> On Friday, September 27, 2019 at 5:13:59 PM UTC-7, Martha S wrote:
> I am trying to export all the data for a particular resource model to CSV for
> review and modification and ran into an error during the process --
> UnicodeEncodeError: 'ascii' codec can't encode character u'\xa6' in position
> 51: ordinal not in range(128)
>
> My command
> python manage.py packages -o export_business_data -d
> '/hpladata/Projects/Downloads/Historic District Mapping Files' -f 'csv' -c
> '/hpladata/Projects/Downloads/Historic District Mapping Files/Historic
> District.mapping'
>
> Here's the full error dump
> operation: export_business_data
> Traceback (most recent call last):
> File "manage.py", line 29, in <module>
> execute_from_command_line(sys.argv)
> File
> "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py",
> line 364, in execute_from_command_line
> utility.execute()
> File
> "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py",
> line 356, in execute
> self.fetch_command(subcommand).run_from_argv(self.argv)
> File
> "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line
> 283, in run_from_argv
> self.execute(*args, **cmd_options)
> File
> "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line
> 330, in execute
> output = self.handle(*args, **options)
> File "/Projects/prod/arches/arches/management/commands/packages.py", line
> 190, in handle
> self.export_business_data(options['dest_dir'], options['format'],
> options['config_file'], options['graphs'], options['single_file'])
> File "/Projects/prod/arches/arches/management/commands/packages.py", line
> 770, in export_business_data
> data = resource_exporter.export(graph_id=graph, resourceinstanceids=None)
> File
> "/Projects/prod/arches/arches/app/utils/data_management/resources/exporter.py",
> line 37, in export
> resources = self.writer.write_resources(graph_id=graph_id,
> resourceinstanceids=resourceinstanceids)
> File
> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py",
> line 194, in write_resources
> csvs_for_export = csvs_for_export +
> self.write_resource_relations(file_name=self.file_name)
> File
> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py",
> line 215, in write_resource_relations
> csvwriter.writerow({k:str(v) for k,v in relation.items()})
> File
> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py",
> line 215, in <dictcomp>
> csvwriter.writerow({k:str(v) for k,v in relation.items()})
> UnicodeEncodeError: 'ascii' codec can't encode character u'\xa6' in position
> 51: ordinal not in range(128)
>
> Any suggestions?
>
> Thanks,
> Martha
>
> --
> -- To post, send email to [email protected] <>. To unsubscribe, send
> email to [email protected] <>. For more information, visit
> https://groups.google.com/d/forum/archesproject?hl=en
> <https://groups.google.com/d/forum/archesproject?hl=en>
> ---
> You received this message because you are subscribed to the Google Groups
> "Arches Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/archesproject/412ef4fb-306b-4961-93f4-f9164065caaa%40googlegroups.com
>
> <https://groups.google.com/d/msgid/archesproject/412ef4fb-306b-4961-93f4-f9164065caaa%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> --
> -- To post, send email to [email protected]. To unsubscribe,
> send email to [email protected]. For more
> information, visit https://groups.google.com/d/forum/archesproject?hl=en
> <https://groups.google.com/d/forum/archesproject?hl=en>
> ---
> You received this message because you are subscribed to the Google Groups
> "Arches Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected]
> <mailto:[email protected]>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/archesproject/d5d0ea82-d653-4112-848a-0a6f02437e3c%40googlegroups.com
>
> <https://groups.google.com/d/msgid/archesproject/d5d0ea82-d653-4112-848a-0a6f02437e3c%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
-- To post, send email to [email protected]. To unsubscribe, send
email to [email protected]. For more information,
visit https://groups.google.com/d/forum/archesproject?hl=en
---
You received this message because you are subscribed to the Google Groups
"Arches Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/archesproject/6035AE74-94AB-4CB3-AF85-8B73B3D9892F%40fargeo.com.
